Hier ein paar code snippets die man immer mal wieder brauchen kann:
Alle FK's einer Datenbank anzeigen:
SELECT OBJECT_NAME(fk.parent_object_id) , fk.name
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fc
ON fk.OBJECT_ID = fc.constraint_object_id
Alle FK's einer Datenbank löschen (Vorsicht!):
SELECT 'ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id)+
' DROP CONSTRAINT ' + fk.name
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fc
ON fk.OBJECT_ID = fc.constraint_object_id
Anzeigen welche FKs von welcher Tabelle aus auf eine gegebene Tabelle verweisen (inkl. Felder):
DECLARE @tabelle nvarchar(100)
set @tabelle = 'TabellennName'
select object_name(fkeyid) Child_Table, object_name(constid) FKey_Name,
c1.name FKey_Col,object_name(rkeyid) This_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