Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check FK Violations Expand / Collapse
Author
Message
Posted Tuesday, September 30, 2008 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 5:28 AM
Points: 0, Visits: 95
Comments posted to this topic are about the item Check FK Violations
Post #578159
Posted Tuesday, September 30, 2008 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 11:15 AM
Points: 45, Visits: 146
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
Posted Thursday, January 8, 2009 10:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 3:12 PM
Points: 70, Visits: 804
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 »

Add to briefcase

Permissions Expand / Collapse