SQL statement to get column type

Question

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

Depends on the RDBMS; SQL Server has the `sys.syscolumns` table for example. — LittleBobbyTables - Au Revoir, Nov 15 '12 at 20:25
Yes, but it'll be different depending on what type of RDBMS you're using - SQL is a language, not the database product, and this question relies on the specific product. You'll be able to find this sort of information in the `INFORMATION_SCHEMA.COLUMNS` table - if your RDBMS has it. — Bridge, Nov 15 '12 at 20:27

score 639 · Accepted Answer · edited Apr 13 '23 at 16:25

639

In ISO SQL (i.e. most RDBMS today) you can use the INFORMATION_SCHEMA.COLUMNS view, which SQL Server supports.
- This view's DATA_TYPE column contains the T-SQL/SQL Server type names, except that it doesn't include arguments for parameterised types, which can result in unexpected/unintentional column behaviour.
  - For example, given three columns typed as nvarchar(max), datetime2(3), and decimal(10,5) then the output will be nvarchar, datetime2, and decimal respectively.
    - This is a problem because a column typed as nvarchar (without (max) or (123)) is the same as nvarchar(1), and using decimal is the same as decimal(18,0) which cannot store non-integer values.
  - The solution is to also look at CHARACTER_OCTET_LENGTH (for binary), CHARACTER_MAXIMUM_LENGTH (for char and nchar), DATETIME_PRECISION (for datetime2 and datetimeoffset), and NUMERIC_PRECISION with NUMERIC_SCALE (for decimal and numeric) in order to reconstruct the type's parameter values.
    - Note that float(n) can be ignored as SQL Server only supports float(24) and float(53) which are aliased by real and float respectively in the DATA_TYPE column.

WITH q AS (
    
    SELECT
        c.TABLE_SCHEMA,
        c.TABLE_NAME,
        c.ORDINAL_POSITION,
        c.COLUMN_NAME,
        c.DATA_TYPE,
        CASE
            WHEN c.DATA_TYPE IN ( N'binary', N'varbinary'                    ) THEN ( CASE c.CHARACTER_OCTET_LENGTH   WHEN -1 THEN N'(max)' ELSE CONCAT( N'(', c.CHARACTER_OCTET_LENGTH  , N')' ) END )
            WHEN c.DATA_TYPE IN ( N'char', N'varchar', N'nchar', N'nvarchar' ) THEN ( CASE c.CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN N'(max)' ELSE CONCAT( N'(', c.CHARACTER_MAXIMUM_LENGTH, N')' ) END )
            WHEN c.DATA_TYPE IN ( N'datetime2', N'datetimeoffset'            ) THEN CONCAT( N'(', c.DATETIME_PRECISION, N')' )
            WHEN c.DATA_TYPE IN ( N'decimal', N'numeric'                     ) THEN CONCAT( N'(', c.NUMERIC_PRECISION , N',', c.NUMERIC_SCALE, N')' )
        END AS DATA_TYPE_PARAMETER,
        CASE c.IS_NULLABLE
            WHEN N'NO'  THEN N' NOT NULL'
            WHEN N'YES' THEN     N' NULL'
        END AS IS_NULLABLE2
    FROM
        INFORMATION_SCHEMA.COLUMNS AS c
)
SELECT
    q.TABLE_SCHEMA,
    q.TABLE_NAME,
    q.ORDINAL_POSITION,
    q.COLUMN_NAME,
    CONCAT( q.DATA_TYPE, ISNULL( q.DATA_TYPE_PARAMETER, N'' ), q.IS_NULLABLE2 ) AS FULL_DATA_TYPE

FROM
    q
WHERE
    q.TABLE_SCHEMA = 'yourSchemaName' AND
    q.TABLE_NAME   = 'yourTableName'  AND 
    q.COLUMN_NAME  = 'yourColumnName'

ORDER BY
    q.TABLE_SCHEMA,
    q.TABLE_NAME,
    q.ORDINAL_POSITION;

Gives results like this:

edited Apr 13 '23 at 16:25

Dai

141,631
28
261
374

answered Nov 15 '12 at 20:31

Francis P

13,377
3
27
51

20

Only if you don't want to see the corresponding column name. This will only return the types. If you want to see the column name the type belongs to you will need to select COLUMN_NAME also... – HackyStack Nov 16 '12 at 13:47
12

And if your table isn't in the default schema you could extend the condition with `AND TABLE_SCHEMA = 'yourSchema'` – luviktor Jun 05 '15 at 07:54
14

this is great - but is it possible to also have it return the range for column's type? i.e. `varchar(255)` instead of `varchar` and `int(11)` instead of `int` ? – Don Cheadle Jun 29 '15 at 18:00
21

@mmcrae : It is possible using column `CHARACTER_MAXIMUM_LENGTH` in `INFORMATION_SCHEMA.COLUMNS`. Just do a `SELECT * FROM INFORMATION_SCHEMA.COLUMNS` to see all the columns available. – Francis P Jul 08 '15 at 12:59
3

What about temporary tables? – Ilya Gazman Dec 22 '16 at 17:57
2

@Ilya_Gazman In what context would you need to identify the type of a temp table column? – Francis P Jan 31 '17 at 13:20
3

It would be useful to see full readable column type like `"numeric(10, 2)"` or `"varchar(100)"` – Evgeny Nozdrev Oct 31 '18 at 10:34
3

You can use this on a view just by using the view name instead of the table name. I almost dismissed this answer for views until I thought of that. – donL Mar 11 '19 at 16:59
1

How to use it in `IF` statement? For example I only want to do some logic if it's an `Integer`? – Pomme De Terre Apr 23 '19 at 12:21
1

@FrancisP I suppose you might if you've created it using the `SELECT ... INTO` syntax and want to check what types SQL Server has decided to use behind the scenes. – Stewart Feb 04 '20 at 10:04
1

If the DATA_TYPE is set for a particular column in table and we want to get all possible values of that column. SQL statement to get that? – Shubhank Gupta Nov 25 '21 at 07:36
1

@PommeDeTerre: This works for me in an IF statement `IF ((SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'ACCOUNT' AND COLUMN_NAME = N'DESCRIPTION') = N'varchar') BEGIN PRINT 'TEST PASS'; END` – Ozair Kafray Feb 25 '22 at 12:50
Very useful. Just for beginners, don't forget that it should be single quote. Double quote raised a error for me (I am calling from Rstudio, R 4.1.2, using JDBC and DBI with Postgres driver) – Corina Roca Dec 16 '22 at 12:32

score 126 · Answer 2 · edited Nov 15 '12 at 22:54

126

The easiest way in TSQL is:

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

edited Nov 15 '12 at 22:54

Preet Sangha

64,563
18
145
216

answered Nov 15 '12 at 20:31

HackyStack

4,887
3
22
28

score 65 · Answer 3 · edited Sep 09 '15 at 15:02

65

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

exec sp_help YOURTABLENAME

edited Sep 09 '15 at 15:02

mwigdahl

16,268
7
50
64

answered Sep 09 '15 at 08:44

Hanif Azhari

651
5
2

7

I upvoted as this is although not exactely the answer but provides valuable information to me. E.g. the "IsComputed" information I didn't find in the Information schema but I can find in the sp_help procedure code and copy from there. – Christoph Sep 21 '15 at 08:48
7

select table name and click `Alt+F1`.. gives same result. – Pugal Sep 28 '18 at 11:37
4

to clarifiy: the table or view name has to be selected in the editor and then press`Alt+F1`. Not in the solution Object Explorer. This is such a helpful feature – bugybunny May 07 '19 at 06:32
1

And not only tables, also working OK on Views, just: 1. Connect to SQL Server in SSMS 2. Open New Query Window 3. Select your database: use 4. Run command: exec sp_help – Don G. Jul 19 '20 at 16:49
1

https://stackoverflow.com/a/73442049/3934886 this syntax worked for me >> EXEC [ServerName].[DatabaseName].dbo.sp_help 'TableName'. – alex351 Aug 22 '22 at 11:16

fimas · Answer 4 · 2012-11-15T20:34:12.667

29

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

edited Nov 15 '12 at 20:34

answered Nov 15 '12 at 20:28

fimas

558
4
8

2

With *otherwise* you mean other RDBMS apart from MySQL? – Lamak Nov 15 '12 at 20:36
4

Yes. The `DESCRIBE` syntax is valid in Oracle as well, however MsSQL will not accept this syntax. – fimas Nov 15 '12 at 20:43
1

See answer from @jTC for method on MSSQL and TSQL. – fimas Nov 15 '12 at 20:45
1

Newer versions of MySQL have `information_schema.COLUMNS`. – Rick James Nov 28 '15 at 13:26
1

I find DESC OR DESCRIBE (depending on the DBMS you use) useful for small tables with 3 or 4 columns then it shows the table structure with column name Nullable flag and column type for big tables however it take more time to return a result and it is harder to find the information your need. – velocity Jul 03 '18 at 11:09

score 28 · Answer 5 · edited May 28 '19 at 15:30

28

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

edited May 28 '19 at 15:30

lorond

3,856
2
37
52

answered Nov 15 '12 at 20:27

jTC

1,340
9
17

4

Actually it is `JOIN sys.types y ON y.user_type_id = c.user_type_id` system_type_id is not unique. [sys.columns doc](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-columns-transact-sql?view=sql-server-2017) – Fabricio May 18 '18 at 11:26

MgSam · Answer 6 · 2022-09-29T13:18:00.217

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', 'varbinary') 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
, [IsNullable]   = c.is_nullable
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'

This should have been the accepted answer. Thanks, maybe you can add the condition ( t.type = 'U' )--remove system table — Iannick, Apr 09 '20 at 13:20
And VARBINARY types could easily be catered for by adding that to the first `WHEN` line: `WHEN tp.[name] IN ('varchar', 'char', 'varbinary') THEN...` — Reversed Engineer, Jun 18 '20 at 09:47
The best answer, I would also add: `, [IsNullable] = c.is_nullable` right after `c.scale`. I think it's very useful. — SeReGa, Aug 17 '22 at 19:36
Added varbinary and isnullable. @Iannick I'm not clear why type = 'U' is necessary? Is it even possible to have a system table with the same name and schema as a user table? — MgSam, Sep 29 '22 at 13:19

score 17 · Answer 7 · answered Sep 08 '14 at 12:36

17

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

answered Sep 08 '14 at 12:36

Assaf

1,352
10
19

score 11 · Answer 8 · answered Oct 18 '16 at 13:06

11

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

answered Oct 18 '16 at 13:06

Arne H. Bitubekk

2,963
1
27
34

csebryam · Answer 9 · 2016-01-11T17:22:03.747

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.

edited Jan 11 '16 at 17:22

answered Dec 31 '15 at 20:16

csebryam

1,091
11
13

score 9 · Answer 10 · answered Feb 22 '21 at 14:30

9

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

answered Feb 22 '21 at 14:30

Nii Joshua

277
3
7

score 7 · Answer 11 · answered Dec 13 '18 at 14:50

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

score 5 · Answer 12 · answered Mar 26 '21 at 12:30

5

From SQL Server 2012 on:

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

answered Mar 26 '21 at 12:30

Arno Tolmeijer

61
1
2

Kprof · Answer 13 · 2012-11-15T22:53:06.160

4

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

edited Nov 15 '12 at 22:53

answered Nov 15 '12 at 20:30

Kprof

742
1
8
16

score 4 · Answer 14 · answered Feb 14 '14 at 07:13

4

For IBM DB2 :

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

answered Feb 14 '14 at 07:13

Aman

143
7

score 4 · Answer 15 · edited May 04 '16 at 08:28

4

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

edited May 04 '16 at 08:28

I.G. Pascual

5,818
5
42
58

answered May 04 '16 at 08:00

Geza Bartha

41
1

score 4 · Answer 16 · answered Jan 18 '19 at 17:09

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

This answer's query returns incorrect results for `binary` and `varbinary` columns, as they use `CHARACTER_OCTET_LENGTH` instead of `CHARACTER_MAXIMUM_LENGTH`. — Dai, Apr 13 '23 at 16:03

score 4 · Answer 17 · edited Oct 10 '19 at 13:36

4

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

edited Oct 10 '19 at 13:36

Das_Geek

2,775
7
20
26

answered Oct 10 '19 at 12:40

Bob Krieter

51
1

Talha Tayyab · Answer 18 · 2023-03-22T09:41:48.970

4

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.

edited Mar 22 '23 at 09:41

answered Apr 06 '21 at 20:19

Talha Tayyab

8,111
25
27
44

score 3 · Answer 19 · answered Jul 29 '20 at 11:58

3

use this query

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

answered Jul 29 '20 at 11:58

Amir-ranjbar

131
1
5

score 3 · Answer 20 · answered Jun 23 '22 at 10:13

3

Below SQL script working perfectly at my end.

SELECT column_name AS 'Column Name',data_type AS 'Data Type' 
FROM information_schema.columns 
WHERE table_name = '[Table Name]'

answered Jun 23 '22 at 10:13

bulbul bd

166
1
1
9

score 2 · Answer 21 · edited May 23 '17 at 12:18

2

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

edited May 23 '17 at 12:18

Community

1
1

answered Jul 29 '15 at 17:23

Anthony Griggs

1,469
2
17
39

score 2 · Answer 22 · answered May 10 '16 at 17:26

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]

score 2 · Answer 23 · edited Aug 29 '18 at 09:54

2

SHOW COLUMNS FROM //table_name// ;

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

edited Aug 29 '18 at 09:54

Tom

9,725
3
31
48

answered Aug 29 '18 at 09:15

Somil Gupta

39
1

score 2 · Answer 24 · answered Oct 21 '20 at 10:12

2

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;

answered Oct 21 '20 at 10:12

gubitza

31
4

score 2 · Answer 25 · answered Aug 22 '22 at 08:15

2

I think the simplest and most useful way in Microsoft SQL Server Management Studio:

EXEC [ServerName].[DatabaseName].dbo.sp_help 'TableName'

answered Aug 22 '22 at 08:15

alex351

1,826
1
21
32

score 1 · Answer 26 · answered Feb 23 '18 at 18:15

1

For Spark SQL:

DESCRIBE [db_name.]table_name column_name

answered Feb 23 '18 at 18:15

ishwr_

123
2
11

score 1 · Answer 27 · answered Jun 08 '18 at 15:07

1

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

answered Jun 08 '18 at 15:07

Michael

4,722
6
37
58

score 1 · Answer 28 · answered Oct 16 '18 at 16:11

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

score 0 · Answer 29 · answered Jan 31 '23 at 05:56

0

simply do this

describe table test.oim_test_2

you will get details for all your columns with their data types

answered Jan 31 '23 at 05:56

Sagar Rawal

1
1

score 0 · Answer 30 · answered Feb 13 '23 at 10:54

can use below scrip to gett he same:

SELECT column_name 'Column_Name', data_type 'Data_Type' 
FROM information_schema.columns
WHERE table_name = 'Name of your desired table'

Note: It will not return what size you've specified for varchar/nvarchar type column(s)

If want to get full details about a table then can use below script:

EXEC [Your Database Name].dbo.sp_help 'Your Table Name'

SQL statement to get column type

30 Answers30

Linked