Microsoft SQL Server - Query to fetch Table Meta Data

Use the below query to fetch table metadata in Microsoft SQL Server.

SELECT

TABLE_SCHEMA,

TABLE_NAME,

ORDINAL_POSITION,

COLUMN_NAME,

CASE DATA_TYPE

WHEN 'DATETIME' THEN UPPER(DATA_TYPE)

ELSE

UPPER(DATA_TYPE)+'('+LTRIM(RTRIM(DATA_LENGTH))+')'

END AS DATA_TYPE,

NULLABLE

FROM

(

SELECT

COLS.TABLE_SCHEMA,

COLS.TABLE_NAME,

COLS.COLUMN_NAME,

COLS.ORDINAL_POSITION,

COLS.DATA_TYPE,

CASE COLS.DATA_TYPE

  WHEN 'CHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'NCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'VARCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'NVARCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'SMALLINT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'INT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'BIGINT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'DECIMAL' THEN RTRIM(CONVERT(CHAR,COLS.NUMERIC_PRECISION)) + ',' +RTRIM(CONVERT(CHAR,COLS.NUMERIC_SCALE))

ELSE

NULL

END AS DATA_LENGTH,

CASE COLS.IS_NULLABLE

WHEN 'NO' THEN 'NOT NULL'

WHEN 'YES' THEN 'NULL'

END AS NULLABLE

FROM

INFORMATION_SCHEMA.COLUMNS COLS

WHERE COLS.TABLE_SCHEMA = 'DBO'

AND COLS.TABLE_NAME = 'EMPLOYEE'

)A

ORDER BY  TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME

Can also use IN Clause for COLS.TABLE_NAME to include multiple tables.

 Thanks for reading my Blog !