Foreign Key Bulma

Bugün foreign key ile ilgili işinize yarayacak iki T-SQL scripti paylaşıyorum:


1. Bir veritabanındaki tüm foreign key'leri bulma:

select
f.name as ForeignKeyName,
schema_name(f.schema_id) SchemaName,
object_name(f.parent_object_id) as TableName,
col_name(fc.parent_object_id, fc.parent_column_id) as ColumnName,
object_name (f.referenced_object_id) as ReferenceTableName,
col_name(fc.referenced_object_id, fc.referenced_column_id) as ReferenceColumnName
from sys.foreign_keys as f 
inner join sys.foreign_key_columns as fc on f.object_id = fc.constraint_object_id



2.Bir tabloya hangi tablolardan foreign key oluşturulmuş?

select f.name as ForeignKeyName,
schema_name(f.schema_id) SchemaName,
object_name(f.parent_object_id) as TableName,
col_name(fc.parent_object_id, fc.parent_column_id) as ColumnName,
object_name (f.referenced_object_id) as ReferenceTableName,
col_name(fc.referenced_object_id, fc.referenced_column_id) as ReferenceColumnName
from sys.foreign_keys as f 
inner join sys.foreign_key_columns as fc on f.object_id = fc.constraint_object_id
where fc.referenced_object_id=object_id('TableName')

--T-SQL kodlar, SQL Server 2012 SP1 ile test edildi.