[sql] SQL statement to get column type

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

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);

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 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 this query

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

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; 

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

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 Spark SQL:

DESCRIBE [db_name.]table_name column_name

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

exec sp_help YOURTABLENAME

The easiest way in TSQL is:

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

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

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';   

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 .

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.

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 = ''

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'

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

For IBM DB2 :

SELECT TYPENAME FROM SYSCAT.COLUMNS WHERE TABSCHEMA='your_schema_name' AND TABNAME='your_table_name' AND COLNAME='your_column_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`;

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')

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]');

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"))