Im Web habe ich bei einer Recherche folgende vier SQL-Skripte gefunden, welche dazu dienen sich die Foreign-Keys einer Tabelle ausgeben zu lassen.
Die ersten beiden verwenden die sys-Tabellen und geben auch zusammengesetzte Schlüssel korrekt aus:
DECLARE @tabelle nvarchar(100)
set @tabelle = "TabellennName"
Beispiel 1:
Select object_name(fkeyid) Child_Table, object_name(constid) FKey_Name,
c1.name FKey_Col, object_name(rkeyid) Parent_Table, c2.name Ref_KeyCol
from sysforeignkeys s
inner join syscolumns c1 on ( s.fkeyid = c1.id and s.fkey = c1.colid )
inner join syscolumns c2 on ( s.rkeyid = c2.id and s.rkey = c2.colid )
where object_name(rkeyid)=@tabelle
order by 1,2,4
Beispiel 2:
select OBJECT_NAME(FKEYS.PARENT_OBJECT_ID) source
,FKEYS.name Constraint_Name
,FK_COLUMN_NAME = convert(sysname,COL2.name)
,OBJECT_NAME(FKEYS.referenced_object_id) destination
,PK_COLUMN_NAME= convert(sysname,col1.name)
from sys.columns COL1,
sys.columns COL2,
sys.foreign_keys FKEYS
inner join sys.foreign_key_columns KEY_COLUMN on (KEY_COLUMN.constraint_object_id = FKEYS.object_id)
where COL1.object_id = FKEYS.referenced_object_id
AND COL2.object_id = FKEYS.parent_object_id
AND COL1.column_id = KEY_COLUMN.referenced_column_id
AND COL2.column_id = KEY_COLUMN.parent_column_id
AND OBJECT_NAME(FKEYS.PARENT_OBJECT_ID)=@tabelle
order by 1,2,4
Die letzten beiden Beispiele verwenden die INFORMATION_SCHEMA-Tabellen und geben aus mehreren Feldern zusammengesetzte Schlüssel nicht ganz korrekt aus:
Beispiel 3:
SELECT distinct K_Table = FK.TABLE_NAME, Constraint_Name = C.CONSTRAINT_NAME,
FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME=@tabelle
ORDER BY 1,2,3,4
Beispiel 4:
SELECT CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME, TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA, TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME, REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA, REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS
ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS
ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME
where fk.table_name=@tabelle