SELECT t.name as tablename, c.name as columnname, st.name, c.is_identity, c.is_nullable
--,c.max_length, c.precision, c.scale
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
join sys.types st on c.system_type_id = st.system_type_id
--and c.name like '%type%'
ORDER BY t.name, c.column_id, c.is_identity
Other experts helped me piece together this SQL to show all tables and their columns for a database. Every so often I tweak it.
how might I show if a column is a foreign key?
are constraints recorded in the sys tables?