SQL statement to get column type

Question

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

Answer 1

Using SQL Server:

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

Answer 2

The easiest way in TSQL is:

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

Answer 3

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

exec sp_help YOURTABLENAME

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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'

Answer 9

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.

Answer 10

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

Answer 11

For IBM DB2 :

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

Answer 12

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

Answer 13

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

Answer 14

use this query

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

Answer 15

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

Answer 16

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

Answer 17

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]

Answer 18

SHOW COLUMNS FROM //table_name// ;

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

Answer 19

For Spark SQL:

DESCRIBE [db_name.]table_name column_name

Answer 20

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

Answer 21

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

Answer 22

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

Answer 23

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

Answer 24

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; 

Answer 25

From SQL Server 2012 on:

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

Answer 26

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.