Fremdschlüssel auflisten

253

Link Reference

https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server

Link Credits

Not set

Link Technical Documentation

Not set

PlCodelang

T-SQL

PlGroup

Metadaten

PlItemTitle

Fremdschlüssel auflisten

Code

SELECT
[FK_NAME] = obj.name
,[Schema_Name] = sch.name
,[Table] = tab1.name
,[Column] = col1.name
,[Referenced_Schema_Name] = sch2.name
,[Referenced_Table] = tab2.name
,[Referenced_Column] = col2.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
INNER JOIN sys.schemas sch2
ON tab2.schema_id = sch.schema_id

Result Example

FK_NAME	Schema_Name	Table	Column	Referenced_Schema_Name	Referenced_Table	Referenced_Column
CountryFK	dbo	Adress	FK_Country	dbo	Countied	ID