SQL statement to get column type

Is there a SQL statement that can return the type of a column in a table?

26 Answers

Using SQL Server:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'yourSchemaName' AND TABLE_NAME = 'yourTableName' AND COLUMN_NAME = 'yourColumnName' 

The easiest way in TSQL is:

SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName' 

For SQL Server, this system stored procedure will return all table information, including column datatypes:

exec sp_help YOURTABLENAME 

In TSQL/MSSQL it looks like:

SELECT t.name, c.name FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id JOIN sys.types y ON y.user_type_id = c.user_type_id WHERE t.name = '' 

If you're using MySQL you could try

SHOW COLUMNS FROM `tbl_name`; 

SHOW COLUMNS on dev.mysql.com

Otherwise you should be able to do

DESCRIBE `tbl_name`; 

in oracle SQL you would do this:

SELECT DATA_TYPE FROM all_tab_columns WHERE table_name = 'TABLE NAME' -- in uppercase AND column_name = 'COLUMN NAME' -- in uppercase 

To build on the answers above, it's often useful to get the column data type in the same format that you need to declare columns.

For example, varchar(50), varchar(max), decimal(p, s).

This allows you to do that:

SELECT [Name] = c.[name] , [Type] = CASE WHEN tp.[name] IN ('varchar', 'char') THEN tp.[name] + '(' + IIF(c.max_length = -1, 'max', CAST(c.max_length AS VARCHAR(25))) + ')' WHEN tp.[name] IN ('nvarchar','nchar') THEN tp.[name] + '(' + IIF(c.max_length = -1, 'max', CAST(c.max_length / 2 AS VARCHAR(25)))+ ')' WHEN tp.[name] IN ('decimal', 'numeric') THEN tp.[name] + '(' + CAST(c.[precision] AS VARCHAR(25)) + ', ' + CAST(c.[scale] AS VARCHAR(25)) + ')' WHEN tp.[name] IN ('datetime2') THEN tp.[name] + '(' + CAST(c.[scale] AS VARCHAR(25)) + ')' ELSE tp.[name] END , [RawType] = tp.[name] , [MaxLength] = c.max_length , [Precision] = c.[precision] , [Scale] = c.scale FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id JOIN sys.columns c ON t.object_id = c.object_id JOIN sys.types tp ON c.user_type_id = tp.user_type_id WHERE s.[name] = 'dbo' AND t.[name] = 'MyTable' 

Another variation using MS SQL:

SELECT TYPE_NAME(system_type_id) FROM sys.columns WHERE name = 'column_name' AND [object_id] = OBJECT_ID('[dbo].[table_name]'); 

Using TSQL/MSSQL

This query will get you: table name, column name, data type, data type length, and allowable nulls

SELECT TABLE_NAME,COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your_table_name' 

The only thing that needs to be changed is your_table_name.

This also works as it selects just the column names and their respective character type

SELECT COLUMN_NAME ,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Items'; 

To retrieve the actual declared data types, for example for use in dynamic SQL to ALTER COLUMNs, something like this can be used:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE + CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','binary','varbinary') AND CHARACTER_MAXIMUM_LENGTH > 0 THEN COALESCE('('+CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH)+')','') ELSE '' END + CASE WHEN DATA_TYPE IN ('decimal','numeric') THEN COALESCE('('+CONVERT(varchar,NUMERIC_PRECISION)+','+CONVERT(varchar,NUMERIC_SCALE)+')','') ELSE '' END AS Declaration_Type, CASE WHEN IS_NULLABLE='NO' THEN 'NOT ' ELSE '' END + 'NULL' AS Nullable FROM INFORMATION_SCHEMA.COLUMNS ORDER BY 1,2 

USE [YourDatabaseName] GO SELECT column_name 'Column Name', data_type 'Data Type' FROM information_schema.columns WHERE table_name = 'YourTableName' GO 

This will return the values Column Name, showing you the names of the columns, and the Data Types of those columns (ints, varchars, etc).

For IBM DB2 :

SELECT TYPENAME FROM SYSCAT.COLUMNS WHERE TABSCHEMA='your_schema_name' AND TABNAME='your_table_name' AND COLNAME='your_column_name' 

Using TSQL/MSSQL

You can use INTO keyword.

The result of SELECT into a real TABLE

Example: select .... INTO real_table_name

After

sp_help real_table_name 

From SQL Server 2012 on:

SELECT * FROM sys.dm_exec_describe_first_result_set( N'SELECT * FROM [my].[Table]', NULL, 0 ); 

Another option for MS SQL is to replace the select query here with the query you want the types for:

declare @sql varchar(4000); set @sql = 'select ''hi'' as greeting'; select * from master.sys.dm_exec_describe_first_result_set (@sql, Null, 0); 

use this query

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE_NAME' ORDER BY ORDINAL_POSITION 

In my case I needed to get the data type for Dynamic SQL (Shudder!) anyway here is a function that I created that returns the full data type. For example instead of returning 'decimal' it would return DECIMAL(18,4): dbo.GetLiteralDataType

Use this query to get Schema, Table, Column,Type, max_length, is_nullable

SELECT QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema' ,QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table' ,C.NAME as 'Column' ,T.name AS 'Type' ,C.max_length ,C.is_nullable FROM SYS.COLUMNS C INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id] INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id WHERE tb.[is_ms_shipped] = 0 ORDER BY tb.[Name] 

SHOW COLUMNS FROM //table_name// ; 

It will give you information about all the columns from the table .

Just if someone finds this useful. In SQL Server:

 sp_columns 'yourtablename' 

This will give details of all the columns. It gives you column_name, data_type, type_name, precision, length, iss_nullable, etc.

For Spark SQL:

DESCRIBE [db_name.]table_name column_name 

For Apache Derby as shown in this answer:

select columndatatype from sys.syscolumns where referenceid = ( select tableid from sys.systables where tablename = 'YOUR_TABEL_NAME' and columnname= 'YOUR_COLUMN_NAME') 

In vb60 you can do this:

Public Cn As ADODB.Connection 'open connection Dim Rs As ADODB.Recordset Set Rs = Cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, UCase("Table"), UCase("field"))) 

'and sample (valRs is my function for rs.fields("CHARACTER_MAXIMUM_LENGTH").value):

 RT_Charactar_Maximum_Length = (ValRS(Rs, "CHARACTER_MAXIMUM_LENGTH")) rt_Tipo = (ValRS(Rs, "DATA_TYPE")) 

Since some people were asking for the precision as well with the data type, I would like to share my script that I have created for such a purpose.

SELECT TABLE_NAME As 'TableName' COLUMN_NAME As 'ColumnName' CONCAT(DATA_TYPE, '(', COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, DATETIME_PRECISION, ''), IIF(NUMERIC_SCALE <> 0, CONCAT(', ', NUMERIC_SCALE), ''), ')', IIF(IS_NULLABLE = 'YES', ', null', ', not null')) As 'ColumnType' FROM INFORMATION_SCHEMA.COLUMNS WHERE -- ... ORDER BY 'TableName', 'ColumnName' 

It's not perfect but it works in most cases.

Using Sql-Server

I find this useful for queries, especially if they use aggregates or several tables (SQL Server)

DECLARE @query nvarchar(max) = 'select * from yourtable'; EXEC sp_describe_first_result_set @query, null, 0;