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]
No comments:
Post a Comment