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

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 2

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 3

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 4

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 5

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 6

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