Alle Primärschlüssel einer Datenbank per SQL herausfinden

[openshinok]

hallo,
weiss jemand zufällig wie man sich per SQL alle Primärschlüssel einer Datenbank anzeigen lassen kann?

[openshinok]

habs doch noch selbst bei google gefunden


SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id WHERE i.is_primary_key = 1

[ebis]

Falls man Primary Keys mit mehr als einer Spalte hat...
[HIGHLIGHT="SQL"]SELECT Idx.Name AS IndexName
, SCHEMA_NAME (Obj.Schema_ID) AS TableSchema
, OBJECT_NAME (Idx.[Object_ID]) AS TableName
, STUFF (x.Col, 1, 2, SPACE(0)) AS Columns
FROM sys.objects AS Obj
JOIN sys.indexes AS Idx ON Obj.Object_ID = Idx.Object_ID
AND IDX.is_primary_key = 1
CROSS APPLY (
SELECT W.Col AS [text()]
FROM (
SELECT NCHAR(44)+NCHAR(32) --", "
+ QUOTENAME (Col.[Name])
+ CASE ico.is_descending_key
WHEN 0 THEN SPACE(0)
ELSE SPACE(1)+NCHAR(68)+NCHAR(69)+NCHAR(83)+NCHAR(67) --" DESC"
END AS col
, Ico.Index_column_ID AS IndexColID
FROM SYS.Indexes AS IxC
JOIN Sys.All_Columns AS Col ON Col.Object_ID = IxC.Object_ID
JOIN Sys.index_columns AS ico ON ico.object_id = IxC.object_id
AND ico.index_id = IxC.index_id
AND ico.column_id = col.column_id
AND ico.Is_Included_Column = 0
WHERE IxC.Index_ID = Idx.Index_ID
AND IxC.Object_ID = Idx.Object_ID
) AS W
ORDER BY W.IndexColID
FOR XML PATH('')
) AS x(Col)[/HIGHLIGHT]