Get DataType from Column Name

Question

I need to get the data type of a column using the column name. I am coding in C#. The situation is I have a view which converts some dates into varchar so I can better display them (without time to be exact). However, that means the data type for the columns are now incorrect. What I'd like to do is look up a column from a DataTable schema using the column name and then get the datatype of that column. I'd be gathering the actual data type from the main table rather than the view in this case. Ideas?

score 4 · Accepted Answer · answered Jun 13 '12 at 17:02

4

The DataTable.Columns property is a DataColumnCollection, which can be indexed by column name to get a DataColumn, which has a DataType property.

answered Jun 13 '12 at 17:02

kol

27,881
12
83
120

Will this work on a DataTable gathered using GetSchemaTable() on a SqlDataReader object? – steventnorris Jun 13 '12 at 17:07
Well it seems the schema table doesn't hold the column names of the sql table. Each row represents a column of the sql table and the columns are the properties (like column name and data type), so this method doesn't seem to work. – steventnorris Jun 13 '12 at 17:31
If you know the index of the column (the type of which you want to determine) then you can get the DataColumn by indexing the Columns collection numerically: http://msdn.microsoft.com/en-us/library/tb313x8d If you don't know the index, then iterate over all columns and print their DataType: foreach (var c in t.Columns) Console.WriteLine(c.DataType.FullName); – kol Jun 13 '12 at 17:36
Yeah. I think that's my only option using this method. I may end up reorganizing this code. Iterating each time is going to slow things down a bit in my case. Thanks! – steventnorris Jun 13 '12 at 17:52

score 1 · Answer 2 · answered Jan 15 '13 at 15:00

information_schema.columns table gives more clear information for what you need than sys.columns table. You could try the following:

SELECT data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'tableName' AND column_name = 'columnName'

score 0 · Answer 3 · answered Jun 13 '12 at 16:59

0

I don't see how that's possible without inferring the type from the values. If you have the results from the view and the column name in the code, the information about the table that the view uses is lost at that point.

You can, of course, get the information about the table if you know its name; that can be accomplished either using a simple query like that:

SELECT TOP 0 * FROM <<tableName>>

or using DMVs:

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('<<tableName>>')

In the first case, you use GetSchemaTable on the IDataReader and iterate over the rows to find your column by name; in the second case, you can iterate over the result set itself and find your column that way.

answered Jun 13 '12 at 16:59

Mr. TA

5,230
1
28
35

I can get the table's schema fine, I just need to know the datatype of a column in that table by column name. Say for instance I have a table with Name, Birthdate, Age columns. I want to know the data type of the column named Birthdate. But all I have is the table schema and column name. – steventnorris Jun 13 '12 at 17:01
It's a DataTable C# object obtained using GetSchemaTable() on an SqlDataReader object. – steventnorris Jun 13 '12 at 17:06

score 0 · Answer 4 · edited May 23 '17 at 12:29

If you are using SQL Server you can make use of SET FMTONLY. It returns only metadata to the client. It can be used to test the format of the response without actually running the query.

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SET FMTONLY ON; select column from table; SET FMTONLY OFF";
SqlDataReader reader = cmd.ExecuteReader();
SqlDbType type = (SqlDbType)(int)reader.GetSchemaTable().Rows[0]["ProviderType"];

Courtesy: StackOverflow

Get DataType from Column Name

4 Answers4