Check FK Violations

/************************************************************************ Documentation header *** 
Module : [ERP.Tools] 

Author : [Lungeanu Adrian-Liviu] 
Created: [2008.06.03] 
Summary: [Check FK Violations] 
[SQL 2000]

== Change log == 
Author : []
Date : []
Summary: []
*** Documentation header *************************************************************************/ 

--// Set environment
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

--// Declare variables
DECLARE 
 @tbl_ForeignKey nvarchar(255)
, @tbl_LastForeignKey nvarchar(255)
, @tbl_TableSchema nvarchar(255)
, @tbl_LastTableSchema nvarchar(255)
, @tbl_TableName nvarchar(255)
, @tbl_LastTableName nvarchar(255)
, @tbl_ColumnName nvarchar(255)
, @tbl_LastColumnName nvarchar(255)
, @tbl_ReferenceTableNameSchema nvarchar(255)
, @tbl_LastReferenceTableNameSchema nvarchar(255)
, @tbl_ReferenceTableName nvarchar(255)
, @tbl_LastReferenceTableName nvarchar(255)
, @tbl_ReferenceColumnName nvarchar(255)
, @tbl_LastReferenceColumnName nvarchar(255)
, @tbl_UpdateAction nvarchar(255)
, @tbl_LastUpdateAction nvarchar(255)
, @tbl_DeleteAction nvarchar(255)
, @tbl_LastDeleteAction nvarchar(255)
, @statement_init nvarchar(800)
, @statement_last nvarchar(800)????
, @statement_final nvarchar(800)
, @crlf nchar(2)
, @RecordsNumber bigint
, @ParmDefinition nvarchar(255)
, @MaxRows int
, @RowCnt int 

--// Declare table variables
DECLARE @FKTable TABLE 
( 
rownum int IDENTITY (1, 1) PRIMARY KEY NOT NULL
, tbl_ForeignKey nvarchar(255)
, tbl_TableSchema nvarchar(255)
, tbl_TableName nvarchar(255)
, tbl_ColumnName nvarchar(255)
, tbl_ReferenceTableNameSchema nvarchar(255)
, tbl_ReferenceTableName nvarchar(255)
, tbl_ReferenceColumnName nvarchar(255)
, tbl_UpdateAction nvarchar(255)
, tbl_DeleteAction nvarchar(255)
) 

--// Initialize variables
SET @statement_init = N''
SET @statement_last = N''
SET @statement_final = N''
SET @tbl_LastForeignKey = N''
SET @tbl_LastTableSchema = N''
SET @tbl_LastTableName = N''
SET @tbl_LastColumnName = N''
SET @tbl_LastReferenceTableNameSchema = N''
SET @tbl_LastReferenceTableName = N''
SET @tbl_LastReferenceColumnName = N''
SET @tbl_LastUpdateAction = N''
SET @tbl_LastDeleteAction = N''
SET @ParmDefinition = N'@RecordsOUT bigint OUTPUT' --// In @RecordsOUT we receive the output of sp_executesql 
SET @RecordsNumber = 0
SET @crlf = nchar(13) + nchar(10) 
SET @RowCnt = 1

--// Declare cursor that store FK references
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]
, 'dbo' [TableSchema]
-- , sc.id [FKTableId]
, so2.name [TableName]
-- , sfk.fkey [FKColumnId]
, sco.name [ColumnName]
, 'dbo' 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 sysobjects so
ON so.id = sc.constid
INNER JOIN sysobjects so2
ON so2.id = sc.id 
INNER JOIN sysforeignkeys sfk
ON sfk.constid = sc.constid 
INNER JOIN sysobjects so3
ON so3.id = sfk.rkeyid 
INNER JOIN syscolumns sco
ON sco.id = sc.id AND
sco.colid = sfk.fkey
INNER JOIN syscolumns sco2
ON sco2.id = sfk.rkeyid AND
sco2.colid = sfk.rkey
WHERE (sc.status & 3) = 3
ORDER BY so.name ASC
, sfk.keyno ASC 


--// OPEN cursor
SELECT @MaxRows = COUNT(*)
FROM @FKTable

--// Check if we have what to parse, if not exit
IF @MaxRows = 0
BEGIN
PRINT N'No ForeignKeys to process...'
GOTO EXIT_TAG
END

--//
WHILE @RowCnt <= @MaxRows
BEGIN --// Main loop

SELECT @tbl_ForeignKey = tbl_ForeignKey
, @tbl_TableSchema = tbl_TableSchema
, @tbl_TableName = tbl_TableName
, @tbl_ColumnName = tbl_ColumnName
, @tbl_ReferenceTableNameSchema = tbl_ReferenceTableNameSchema
, @tbl_ReferenceTableName = tbl_ReferenceTableName
, @tbl_ReferenceColumnName = tbl_ReferenceColumnName
, @tbl_UpdateAction = tbl_UpdateAction
, @tbl_DeleteAction = tbl_DeleteAction
FROM @FKTable
WHERE rownum = @RowCnt 

IF @tbl_LastForeignKey = @tbl_ForeignKey --// FK with more than a column
BEGIN
SET @statement_init = @statement_init + @crlf + N' AND aa.[' + @tbl_ColumnName + N'] = bb.[' + @tbl_ReferenceColumnName + N'] '
SET @statement_last = @statement_last + @crlf + N' AND aa.[' + @tbl_ColumnName + N'] IS NOT NULL '
END

ELSE --// We are on other FK
BEGIN
IF @tbl_LastForeignKey <> '' --// We avoid empty string
BEGIN --// Skipped once at begining
SET @statement_final = @statement_init + @crlf + N' WHERE bb.[' + @tbl_LastReferenceColumnName + N'] IS NULL ' + @crlf + @statement_last + N') '
SET @RecordsNumber = 0 --// Reset @RecordsNumber to 0 
EXEC dbo.sp_executesql @statement_final, @ParmDefinition, @RecordsOUT = @RecordsNumber OUTPUT
IF @RecordsNumber > 0
PRINT @crlf + N'--// FK Violation! (' + CAST(@RecordsNumber AS nvarchar(20)) + N') ' + 
@tbl_LastForeignKey + @crlf + REPLACE (@statement_final , N' @RecordsOUT = COUNT(*) ' , N' * ')
END

--// Save last record
SET @tbl_LastForeignKey = @tbl_ForeignKey
SET @tbl_LastTableSchema = @tbl_TableSchema
SET @tbl_LastTableName = @tbl_TableName
SET @tbl_LastColumnName = @tbl_ColumnName
SET @tbl_LastReferenceTableNameSchema = @tbl_ReferenceTableNameSchema
SET @tbl_LastReferenceTableName = @tbl_ReferenceTableName
SET @tbl_LastReferenceColumnName = @tbl_ReferenceColumnName
--SET @tbl_LastUpdateAction = @tbl_UpdateAction
--SET @tbl_LastDeleteAction = @tbl_DeleteAction

--// Prepare begining of next statement
SET @statement_init = N'SELECT @RecordsOUT = COUNT(*) ' + @crlf + 
N' FROM [' + @tbl_TableSchema + N'].[' + @tbl_TableName + N'] AS aa (NOLOCK) ' + @crlf + 
N' LEFT JOIN [' +@tbl_ReferenceTableNameSchema + N'].[' + @tbl_ReferenceTableName + N'] AS bb (NOLOCK) ' + @crlf + 
N' ON aa.[' + @tbl_ColumnName + N'] = bb.[' + @tbl_ReferenceColumnName + N'] '

--// Need this for fields that accept NULL values
SET @statement_last = N' AND (aa.[' + @tbl_ColumnName + N'] IS NOT NULL'
END

--// Increment @RowCnt
SELECT @RowCnt = @RowCnt + 1
END

--// Process last record
SET @statement_final = @statement_init + @crlf + N' WHERE bb.' + @tbl_LastReferenceColumnName + N' IS NULL ' + @crlf + @statement_last + N') '
SET @RecordsNumber = 0 --// set @RecordsNumber to 0 
EXEC dbo.sp_executesql @statement_final, @ParmDefinition, @RecordsOUT = @RecordsNumber OUTPUT
IF @RecordsNumber > 0
PRINT @crlf + N'--// FK Violation! (' + CAST(@RecordsNumber AS nvarchar(20)) + N') ' + 
@tbl_LastForeignKey + @crlf + REPLACE (@statement_final , N' @RecordsOUT = COUNT(*) ' , N' * ')

EXIT_TAG:
--// Restore environment
SET NOCOUNT OFF
SET ANSI_WARNINGS ON