select distinct foreignKey = foreignKey.name ,onTable = onTable.name ,againstTable = againstTable.name ,onColumn = onColumn.name ,againstColumn = againstColumn.name ,enf = case when ObjectProperty(fk.constid,'CnstIsDisabled') <> 0 then '* NOCHECK *' else 'enforced' end ,del = case when ObjectProperty(fk.constid,'CnstIsDeleteCascade') = 1 then 'cascade deletes' else '' end ,upd = case when ObjectProperty(fk.constid,'CnstIsUpdateCascade') = 1 then 'cascade updates' else '' end from sysforeignkeys fk inner join sysobjects foreignKey on foreignKey.id = fk.constid inner join sysobjects onTable on fk.fkeyid = onTable.id inner join syscolumns onColumn on fk.fkeyid = onColumn.id and fk.fkey = onColumn.colid inner join sysobjects againstTable on fk.rkeyid = againstTable.id inner join syscolumns againstColumn on fk.rkeyid = againstColumn.id and fk.rkey = againstColumn.colid where 1=1 AND againstTable.TYPE = 'U' AND onTable.TYPE = 'U' order by 1, 2, 3
/*================================================================================================== OBJECT: Fkeys view SOURCE: http://www.jasinskionline.com/TechnicalWiki/List-Every-Foreign-Key.ashx ==================================================================================================*/ create view dbo.Fkeys as with RefAction as ( select RefCode = 0, RefDesc = 'Do nothing' union select 1, 'Cascade' union select 2, 'Set null' union select 3, 'Set default' ) ,fk as ( select ForeignKey = fk.name ,OnSchema = ots.name ,OnTable = OnTable.name ,AgainstSchema = ats.name ,AgainstTable = AgainstTable.name ,OnColumn = OnColumn.name ,AgainstColumn = AgainstColumn.name ,Enforced = case when fk.is_disabled = 1 then 'Disabled' else 'Enforced' end ,DeleteAction = da.RefDesc ,UpdateAction = ua.RefDesc from sys.foreign_keys fk inner join sys.foreign_key_columns fkcols on fk.object_id = fkcols.constraint_object_id inner join sys.objects OnTable on fk.parent_object_id = OnTable.object_id inner join sys.schemas ots on OnTable.schema_id = ots.schema_id inner join sys.objects AgainstTable on fk.referenced_object_id = AgainstTable.object_id inner join sys.schemas ats on AgainstTable.schema_id = ats.schema_id inner join sys.columns OnColumn on fkcols.parent_column_id = OnColumn.column_id and fkcols.parent_object_id = OnColumn.object_id inner join sys.columns AgainstColumn on fkcols.referenced_column_id = AgainstColumn.column_id and fkcols.referenced_object_id = AgainstColumn.object_id inner join RefAction da on fk.delete_referential_action = da.RefCode inner join RefAction ua on fk.update_referential_action = ua.RefCode ) select * ,DropCode = 'alter table [' + OnSchema + '].[' + OnTable + '] drop constraint [' + ForeignKey + ']' ,CreateCode = 'alter table [' + OnSchema + '].[' + OnTable + '] with check add constraint [' + ForeignKey + '] foreign key ([' + OnColumn + ']) references [' + AgainstSchema + '].[' + AgainstTable + '] ([' + AgainstColumn + '])' from fk
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.