Mahmoud ElMansy: knowledge meant to be free

Using information_schema to get all columns name and data types

Hi all and here we are again with one of the interesting SQL “information_schema
in this post we will talk about “information_schema.columns” it’s avery interisting view it containes all the data for the tables columns on your DataBase
Here is a list of the column in “information_schema.columns”

TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE
CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX
NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_CATALOG,CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME,COLLATION_CATALOG,COLLATION_SCHEMA,COLLATION_NAME
DOMAIN_CATALOG,DOMAIN_SCHEMA,DOMAIN_NAME

and now with query to list all tables and column name and data type

SELECT TABLE_NAME, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.COLUMNS AS extern
CROSS APPLY
(
SELECT column_name +' '+DATA_TYPE + ','
FROM information_schema.COLUMNS AS intern
WHERE extern.TABLE_NAME = intern.TABLE_NAME
FOR XML PATH('')
) pre_trimmed (column_names)
--WHERE table_name='YourTable'
GROUP BY TABLE_NAME, column_names;