I know it has been quite a while since I posted to the blog, a topic for another time, but I wanted to put this out here as I have used it three times this week to answer a couple different questions.
The system view sys.key_constraints replaces functionality found in sysconstraints in SQL Server 2000 and earlier versions. While sysconstraints still exists in SQL Server 2008 it is slated for removal in a future version of SQL Server. The view stores details for primary and unique keys.
What we are after today is the name, parent schema, parent table, and (most importantly) the column list making up our keys.
Here is the code:
SELECT kc.name AS KeyName
,SCHEMA_NAME(kc.[schema_id]) AS ParentSchemaName
,OBJECT_NAME(kc.[parent_object_id]) AS ParentObjectName
,type_desc AS KeyType
,create_date AS KeyCreationDate
,STUFF((
SELECT ', ' + c.name
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c On c.column_id = ic.column_id
AND c.[object_id] = ic.[object_id]
WHERE ic.index_id = kc.unique_index_id
AND ic.[object_id] = kc.[parent_object_id]
ORDER BY ic.key_ordinal
FOR
XML PATH('')
), 1, 1, '') AS ColumnDefinition
FROM sys.key_constraints AS kc
WHERE type = 'UQ'
ORDER BY ParentObjectName ;As referenced above, I have some changes coming in my focus. Expect to see more code and less talk out of this blog in the near future. Enjoy!
974d319d-aea3-4c01-9f5d-5761de27d4cb|0|.0