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

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 2

SHOW COLUMNS FROM //table_name// ;

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

Answer 3

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 4

The easiest way in TSQL is:

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

Answer 5

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.system_type_id = c.system_type_id
WHERE t.name = ''

Answer 6

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