Sous forme de fonction :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| CREATE FUNCTION F_PK_COMPOSITION(@SCHEMA_NAME sysname, @OBJECT_NAME sysname)
RETURNS TABLE
AS RETURN
(
SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME,
STUFF((SELECT ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
WHERE TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND
TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
FOR XML PATH('')), 1, 1, '') AS PK_COMPOSEE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.TABLE_SCHEMA = COALESCE(@SCHEMA_NAME, TC.TABLE_SCHEMA) AND
TC.TABLE_NAME = COALESCE(@OBJECT_NAME, TC.TABLE_NAME)
);
GO |
Appel :
SELECT * FROM dbo.F_PK_COMPOSITION('dbo', 'Matable')
--> renvoie la clef d'une table
SELECT * FROM dbo.F_PK_COMPOSITION(NULL, NULL)
--> renvoie toutes les clefs de toutes les tables
A +
Partager