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