Check FK Violations

Question

Post reply

Check FK Violations

Man inTheMiddle

Grasshopper

Points: 10
More actions
September 30, 2008 at 3:41 am

#191556

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Matt Minnis SSC Enthusiast Points: 149 More actions · Answer 1

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?

don.schaeffer Ten Centuries Points: 1195 More actions · Answer 2

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 ANDsco.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