Check FK Violations

  • Man inTheMiddle

    Grasshopper

    Points: 10

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

  • Matt Minnis

    SSC Enthusiast

    Points: 149

    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: 1165

    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

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

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