SQL Tips #21 - Listing All Table Columns and Their Default Values

This query lists every table column that has a default value defined, along with the default itself.

(optional so.name='xxx' to filter to a specific table)

The Query

SELECT so.name AS table_name, sc.name AS column_name, sm.text AS default_value 
FROM sys.sysobjects so JOIN sys.syscolumns sc ON sc.id = so.id 
LEFT JOIN sys.syscomments sm ON sm.id = sc.cdefault 
WHERE so.xtype = 'U'AND sm.text IS NOT NULL --AND so.name = @yourtable 
ORDER BY so.[name], sc.colid

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags