How to get columns types of table in DB via SQL query?

Question

Me need SQL statement to get table column type. How I can to get column types of table in DBMS via SQL query? Is there a SQL statement that can return the type of a column in a table?

---

sql get column type
sql get column comment

Answer 1

Using MySQL:

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

SELECT DATA_TYPE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName' AND COLUMN_NAME = 'yourColumnName' AND TABLE_SCHEMA = 'yourSchema';

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'yourTableSchema' AND TABLE_NAME = 'yourTableName';

---

The easiest way in T-SQL is:

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

---

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

exec sp_help YOURTABLENAME

---

In T-SQL/MS SQL Server 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 = 'yourTableName'

---

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

---

If you're using MySQL you could try:

SHOW COLUMNS FROM `tbl_name`;

Otherwise you should be able to do:

DESCRIBE `tbl_name`;

or better yet:

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 IBM DB2:

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

---

Using T-SQL/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.

---

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]

---

For Spark SQL:

DESCRIBE [db_name.]table_name column_name