How to retrieve column data types from query?

Question

I would like to retrieve data types of columns returned from (any) SQL query (using OLE DB). My assumption was, that calling GetSchemaTable method on OleDbDataReader object provides this info in ProviderType column. To transform this number to actual name, I search that value in DataTypes schema table.

My code looks following:

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 table is defined like this (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]

Here is actual output:

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

My problem is that there are multiple provider types with the same ProviderDbType value and I'm unable to choose the correct one. For example for column NvarcharValue, I expect to get nvarchar type. But date, time, datetime2, datetimeoffset and nvarchar have all the same value 202. What am I doing wrong?

Edit: Just for clarification, I would like to get types of all returned values, not only columns of one particular table. Therefore I cannot simply query INFORMATION_SCHEMA (or sys.columns and sys.types on SQL Server). I modified the code accordingly to make it more obvious.

possible duplicate of [How can I get column names from a table in SQL Server?](http://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server) You need to query metadata from server to know exact column definition. — T.S., Sep 01 '15 at 01:36
I think the linked solution doesn't exactly solve my problem. I've edited my question to be more specific. — Stalker, Sep 01 '15 at 16:15
What you're trying to do is to create sophisticated system while relying on some general functionality. We can start that you're using OleDb, which is not recommended and soon will be discontinued. I think, Sql server 2014 doesn't support this provider. Data providers are best when they are vendor-specific. SqlClient for MSSQL and Odp.Net for Oracle, and MySql.Data.dll for MySql... You want to use `SqlDatatype` types to be more precise, and not OleDb generic types. And For Oracle you need to use `OracleDatatype`. Ideally, you abstract specification from implementation, so in YOUR system, you.. — T.S., Sep 01 '15 at 17:50
.. only say `MyDataTypes.Binary` and your framework will use `SqlDatatype.binary` or `Oracle.Blob`. I remember, one time we developed app in which we had our own metadata on each table and column and this metadata was driving query creation. — T.S., Sep 01 '15 at 17:54
I thought that instead of writing my own abstraction, I'll leverage already existing one (OLE DB). I don't mind that it is outdated for this particular project. But it seems that you're right and I'll have to use vendor specific providers for what I want. Anyway, thanks for helping me. — Stalker, Sep 02 '15 at 15:47

score 0 · Answer 1 · answered Sep 01 '15 at 08:54

0

I hope this helps. Here is an example script to identify all the columns datatype and other information in a specific database in 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;

answered Sep 01 '15 at 08:54

Kym NT

670
9
28

Thanks, but this doesn't quite solve my problem. I've edited the question to be more specific. Also, I think this works only on SQL Server. – Stalker Sep 01 '15 at 16:11

How to retrieve column data types from query?

1 Answers1