Thursday, October 09, 2008

Listing Column Defaults

I had a need to list all the defaults in a database on columns that are rowguidcols where the default is not NewSequentialID(). This code is useful for joining these tables, regardless of what the WHERE clause is.

Books Online's entry for sys.default_constraints threw me off because the JOIN logic is incomplete. I've highlighted the missing part in the query below. Here is the full query that returned just what I needed:
  SELECT
t.[name] AS TableName,
c.[name] AS ColumnName,
i.[name] AS IndexName,
ct.[name] AS TypeName,
dc.definition AS DefaultDefinition
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
JOIN sys.index_columns AS ic
ON i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
JOIN sys.columns AS c
ON ic.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
JOIN sys.types AS ct
ON c.system_type_id = ct.system_type_id
JOIN sys.default_constraints AS dc
ON dc.parent_object_id = t.[object_id]
AND dc.parent_column_id = c.column_id
WHERE c.is_rowguidcol = 1
AND dc.definition NOT LIKE '%newsequentialid%'
ORDER BY
t.[name],
c.[name],
i.[name]