Check FK Violations

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Man inTheMiddle » Check FK Violations

Check FK Violations

Rate Topic

Display Mode

Topic Options

Author

Message

Man inTheMiddle

Posted Tuesday, September 30, 2008 3:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 4:23 AM
Points: 0, Visits: 93

Comments posted to this topic are about the item Check FK Violations

Post #578159

Matt Minnis

Posted Tuesday, September 30, 2008 9:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 08, 2013 1:05 PM
Points: 45, Visits: 131

When I ran it I got the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'aes.MSSQLLM$COLUMNLOG'.
I changed the dbo references to aes since all our db objects are owned by a user aes.
Is there a way to get it to skip/log errors like this instead of bombing out entirely as it did?

Post #578409

don.schaeffer

Posted Thursday, January 08, 2009 10:49 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 7:05 AM
Points: 60, Visits: 633

You could replace the insert statement with this one to get the correct table schemas and avoid those errors. We too use multiple schemas, for organizational purposes.

INSERT INTO @FKTable (tbl_ForeignKey, tbl_TableSchema, tbl_TableName, tbl_ColumnName, tbl_ReferenceTableNameSchema, tbl_ReferenceTableName, tbl_ReferenceColumnName, tbl_UpdateAction, tbl_DeleteAction)
SELECT --sc.constid [FKId]
-- , sc.colid
so.name [ForeignKey]
, ss2.name [TableSchema]
-- , sc.id [FKTableId]
, so2.name [TableName]
-- , sfk.fkey [FKColumnId]
, sco.name [ColumnName]
, ss3.name AS [ReferenceTableNameSchema]
-- , sfk.fkeyid
-- , sfk.rkeyid [PKTableId]
, so3.name [ReferenceTableName]
-- , sfk.rkey [PKColumnId]
, sco2.name [ReferenceColumnName]
, CASE objectproperty(sc.constid, 'CnstIsUpdateCascade') WHEN 0 THEN 'NO ACTION' WHEN 1 THEN 'CASCADE' WHEN 2 THEN 'SET NULL' WHEN 3 THEN 'SET DEFAULT' ELSE '' END [UpdateAction]
, CASE objectproperty(sc.constid, 'CnstIsDeleteCascade') WHEN 0 THEN 'NO ACTION' WHEN 1 THEN 'CASCADE' WHEN 2 THEN 'SET NULL' WHEN 3 THEN 'SET DEFAULT' ELSE '' END [DeleteAction]
-- , CASE objectproperty(sc.constid, 'CnstIsNotRepl') WHEN 1 THEN 'NOT FOR REPLICATION' ELSE '' END AS [EnforceForReplication]
-- , sfk.keyno
FROM sysconstraints sc
INNER JOIN sys.objects so ON so.object_id = sc.constid
INNER JOIN sys.objects so2 ON so2.object_id = sc.id
INNER JOIN sys.schemas ss2 ON ss2.schema_id = so2.schema_id
INNER JOIN sysforeignkeys sfk ON sfk.constid = sc.constid
INNER JOIN sys.objects so3 ON so3.object_id = sfk.rkeyid
INNER JOIN sys.schemas ss3 ON ss3.schema_id = so3.schema_id
INNER JOIN sys.columns sco ON sco.object_id = sc.id AND sco.column_id = sfk.fkey
INNER JOIN sys.columns sco2 ON sco2.object_id = sfk.rkeyid AND sco2.column_id = sfk.rkey
WHERE (sc.status & 3) = 3
ORDER BY so.name ASC, sfk.keyno ASC

Post #632661

« Prev Topic | Next Topic »

Permissions