Lists all columns and their datatypes for all tables in Data Explorer
Q&A for professional and enthusiast programmers
table_name as [Table],
ordinal_position as [Field #],
column_name as [Column],
when data_type like '%varchar%'
then data_type+'('+cast(character_maximum_length as varchar)+')'
when data_type like '%int%'
then data_type+'('+cast(numeric_precision as varchar)+')'
end as [type]
--where table_name like '%Types%' --ⓘuncomment this line to limit to reference tables
order by table_name, ordinal_position
--Fields not included since all records have the same value:
-- TABLE_SCHEMA = 'dbo'
-- COLUMN_DEFAULT = Null
-- DATETIME_PRECISION = 3 (when DATETIME)
-- NUMERIC_PRECISION_RADIX = (when TINYINT, SMALLINT, INT)
-- NUMERIC_SCALE = Null
-- ⓘ=tables ending with '%Types' are static reference tables