如何从查询中检索列数据类型？
我想检索从（任何）SQL查询（使用OLE DB）返回的列的数据类型。 我的假设是，在OleDbDataReader对象上调用GetSchemaTable方法在ProviderType列中ProviderType了此信息。 为了将此数字转换为实际名称，我在DataTypes模式表中搜索该值。
我的代码如下所示：
Private Sub Test()
Dim cs = "Provider=SQLOLEDB;Initial Catalog=TestDb;Data Source=127.0.0.1;User ID=XXX;Password=XXX"
Dim schemaTable As DataTable
Dim dataTypesTable As DataTable
Using conn = New OleDbConnection(cs)
conn.Open()
Using command = conn.CreateCommand()
command.CommandText = "SELECT *, 42 AS Foo, CURRENT_TIMESTAMP AS Bar FROM DataItem"
Using reader = command.ExecuteReader(CommandBehavior.SchemaOnly And CommandBehavior.KeyInfo)
schemaTable = reader.GetSchemaTable()
End Using
End Using
dataTypesTable = conn.GetSchema("DataTypes")
End Using
For Each row As DataRow In schemaTable.Rows
Dim name = row.Field(Of String)("ColumnName")
Dim providerType = row.Field(Of Int32)("ProviderType")
Dim types = dataTypesTable.Rows.OfType(Of DataRow).
Where(Function(r) r.Field(Of Int32)("ProviderDbType") = providerType).
Select(Function(r) r.Field(Of String)("TypeName"))
Console.WriteLine($"Column: {name}, Provider type: {providerType}, Types: {String.Join(", ", types)}")
Next
End Sub
DataItem表定义如下（SQL Server 2012）：
CREATE TABLE [dbo].[DataItem](
[Id] [uniqueidentifier] NOT NULL,
[NvarcharValue] [nvarchar](50) NOT NULL,
[NvarcharNullValue] [nvarchar](50) NULL,
[DateValue] [date] NOT NULL,
[DateNullValue] [date] NULL,
[TimeValue] [time](7) NOT NULL,
[TimeNullValue] [time](7) NULL,
[DatetimeValue] [datetime] NOT NULL,
[DatetimeNullValue] [datetime] NULL,
[SmallintValue] [smallint] NOT NULL,
[SmallintNullValue] [smallint] NULL,
[IntValue] [int] NOT NULL,
[IntNullValue] [int] NULL,
[BigintValue] [bigint] NOT NULL,
[BigintNullValue] [bigint] NULL,
[RealValue] [real] NOT NULL,
[RealNullValue] [real] NULL,
[FloatValue] [float] NOT NULL,
[FloatNullValue] [float] NULL,
[NumericValue] [numeric](10, 3) NOT NULL,
[NumericNullValue] [numeric](10, 3) NULL,
[BitValue] [bit] NOT NULL,
[BitNullValue] [bit] NULL,
[ImageValue] [image] NOT NULL,
[ImageNullValue] [image] NULL,
[VarbinaryValue] [varbinary](50) NOT NULL,
[VarbinaryNullValue] [varbinary](50) NULL,
[GeometryNullValue] [geometry] NULL,
[GeographyNullValue] [geography] NULL,
[NvarcharMaxNullValue] [nvarchar](max) NULL,
CONSTRAINT [PK_DataItem] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
这里是实际输出：
Column: Id, Provider type: 72, Types: uniqueidentifier Column: NvarcharValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: NvarcharNullValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: DateValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: DateNullValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: TimeValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: TimeNullValue, Provider type: 202, Types: date, time, datetime2, datetimeoffset, nvarchar Column: DatetimeValue, Provider type: 135, Types: smalldatetime, datetime Column: DatetimeNullValue, Provider type: 135, Types: smalldatetime, datetime Column: SmallintValue, Provider type: 2, Types: smallint Column: SmallintNullValue, Provider type: 2, Types: smallint Column: IntValue, Provider type: 3, Types: int Column: IntNullValue, Provider type: 3, Types: int Column: BigintValue, Provider type: 20, Types: bigint Column: BigintNullValue, Provider type: 20, Types: bigint Column: RealValue, Provider type: 4, Types: real Column: RealNullValue, Provider type: 4, Types: real Column: FloatValue, Provider type: 5, Types: float Column: FloatNullValue, Provider type: 5, Types: float Column: NumericValue, Provider type: 131, Types: decimal, numeric Column: NumericNullValue, Provider type: 131, Types: decimal, numeric Column: BitValue, Provider type: 11, Types: bit Column: BitNullValue, Provider type: 11, Types: bit Column: ImageValue, Provider type: 205, Types: image Column: ImageNullValue, Provider type: 205, Types: image Column: VarbinaryValue, Provider type: 204, Types: varbinary Column: VarbinaryNullValue, Provider type: 204, Types: varbinary Column: GeometryNullValue, Provider type: 205, Types: image Column: GeographyNullValue, Provider type: 205, Types: image Column: NvarcharMaxNullValue, Provider type: 203, Types: ntext, xml Column: Foo, Provider type: 3, Types: int Column: Bar, Provider type: 135, Types: smalldatetime, datetime
我的问题是有多个具有相同ProviderDbType值的提供者类型，我无法选择正确的提供者类型。 例如，对于列NvarcharValue ，我期望获得nvarchar类型。 但date ， time ， datetime2 ， datetimeoffset和nvarchar具有相同的值202 。 我究竟做错了什么？
编辑：只是为了澄清，我想获得所有返回值的类型，而不仅仅是一个特定表的列。 因此，我不能简单地查询INFORMATION_SCHEMA （或SQL Server上的sys.columns和sys.types ）。 我相应地修改了代码，使其更加明显。
我希望这有帮助。 以下是一个示例脚本，用于标识SQL SERVER中特定数据库中的所有列数据类型和其他信息
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName
,c.name AS ColumnName
,SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TypeName
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.PRECISION
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
ORDER BY c.OBJECT_ID;