Data Differences Among Tables

The following has been most useful in the past for me in updating lookup tables (hence the Stored Procedure name) but can be used for any table that has the same structure across multiple DBs.

I’ll write a more in depth description of what it does (and how it does it) later when I have time, and am redirected towards this type of task again…

I have not used this code in some time, but as I recall, it needs to be run FROM one of the databases that you are looking for the differences across, since it uses the INFORMATION_SCHEMA views from the current database to determine the tables and fields.

The code:
/*
DECLARE @return_value int, @SQLStatements nvarchar(4000)
EXEC @return_value = [dbo].[LookupTableDiff] @SQLStatements = @SQLStatements OUTPUT, @TableNameToProcess = N’Question’
SELECT @SQLStatements as N’@SQLStatements’
*/
ALTER PROCEDURE [dbo].[LookupTableDiff]
@SQLStatements NVARCHAR(4000) = ” OUTPUT,
@TableNameToProcess nvarchar(4000)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
DECLARE @sqlField NVARCHAR(4000)
DECLARE @Nullable NVARCHAR(3)
DECLARE @Default NVARCHAR(4000)
DECLARE @DataType NVARCHAR(100)
DECLARE @Server1Name NVARCHAR(4000)
DECLARE @DB1Name NVARCHAR(4000)
DECLARE @Server2Name NVARCHAR(4000)
DECLARE @DB2Name NVARCHAR(4000)
DECLARE @TableName NVARCHAR(4000)
DECLARE @FieldName NVARCHAR(4000)
DECLARE @FirstFieldName NVARCHAR(4000)
DECLARE @Count INT
DECLARE @SERVERS TABLE (
ServerName NVARCHAR (20) NOT NULL ,
DBName NVARCHAR (50) NOT NULL)
INSERT INTO @SERVERS SELECT ‘CAODev’,'[ServerName].CAODev’
INSERT INTO @SERVERS SELECT ‘CAOTest’,'[ServerName].CAOTest’
DECLARE @TABLESTORUN TABLE (
TableName NVARCHAR (100) NOT NULL)
INSERT INTO @TABLESTORUN (TableName) SELECT @TableNameToProcess
CREATE TABLE #TABLES (
TableName NVARCHAR (100) NOT NULL ,
ServerWith NVARCHAR (100) NULL ,
ServerWithOut NVARCHAR (100) NULL ,
ExtraCount INT NULL)
SET @SQLStatements = ”
DECLARE SERVERRUN CURSOR FOR
SELECT SERVER1.ServerName AS Server1Name, SERVER1.DBName AS DB1Name, SERVER2.ServerName AS Server2Name, SERVER2.DBName AS DB2Name
FROM @SERVERS SERVER1, @SERVERS SERVER2
WHERE SERVER1.ServerName<>SERVER2.ServerName AND SERVER1.DBName<>SERVER2.DBName
OPEN SERVERRUN
FETCH NEXT FROM SERVERRUN INTO @Server1Name, @DB1Name, @Server2Name, @DB2Name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE TABLERUN CURSOR FOR
SELECT TableName FROM @TABLESTORUN
OPEN TABLERUN
FETCH NEXT FROM TABLERUN INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘INSERT INTO #TABLES
SELECT ”’ + @TableName + ”’, ”’ + @Server1Name + ”’, ”’ + @Server2Name + ”’, count(*)
FROM ‘ + @DB1Name + ‘.dbo.’ + @TableName + ‘ ‘ + @Server1Name + ‘
LEFT JOIN ‘ + @DB2Name + ‘.dbo.’ + @TableName + ‘ ‘ + @Server2Name + ‘ ON ‘
SET @sqlField = ”
SET @FirstFieldName = ”
DECLARE FIELD CURSOR FOR
SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @TableName)
OPEN FIELD
FETCH NEXT FROM FIELD INTO @FieldName, @Nullable, @Default, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT (@FieldName = ‘createdDate’) AND NOT(@FieldName = ‘updatedDate’) AND NOT(@FieldName = ‘updatedBy’)
BEGIN
IF @sqlField = ”
BEGIN
SET @sqlField = ”
SET @FirstFieldName = @FieldName
END
ELSE
BEGIN
SET @sqlField = @sqlField + ‘ AND ‘
END
IF (@Nullable = ‘Yes’) AND (@Default IS NULL) AND (@DataType like ‘%char’)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘ + ” ”,””) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘ + ” ”,””)) ‘
ELSE IF (@Nullable = ‘Yes’) AND (@Default IS NULL) AND (@DataType = ‘datetime’)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘ ,”1/1/1900”) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘,”1/1/1900”)) ‘
ELSE IF (@Nullable = ‘Yes’) AND (@Default IS NULL)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘,”0”) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘,”0”)) ‘
ELSE
SET @sqlField = @sqlField + ‘(‘ + @Server1Name + ‘.’ + @FieldName + ‘ = ‘ + @Server2Name + ‘.’ + @FieldName + ‘) ‘
END
FETCH NEXT FROM FIELD INTO @FieldName, @Nullable, @Default, @DataType
END
CLOSE FIELD
DEALLOCATE FIELD
SET @sql = @sql + @sqlField + ‘WHERE (((‘ + @Server2Name + ‘.’ + @FirstFieldName + ‘) Is Null)) ‘
EXEC sp_executesql @sql
FETCH NEXT FROM TABLERUN INTO @TableName
END
CLOSE TABLERUN
DEALLOCATE TABLERUN
FETCH NEXT FROM SERVERRUN INTO @Server1Name, @DB1Name, @Server2Name, @DB2Name
END
CLOSE SERVERRUN
DEALLOCATE SERVERRUN
DECLARE TABLERUN CURSOR FOR
SELECT TableName, ServerWith, ServerWithOut FROM #TABLES WHERE ExtraCount >0 ORDER BY TableName, ServerWith, ServerWithOut
OPEN TABLERUN
FETCH NEXT FROM TABLERUN INTO @TableName, @Server1Name, @Server2Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DB1Name = DBName FROM @SERVERS WHERE ServerName = @Server1Name
SELECT @DB2Name = DBName FROM @SERVERS WHERE ServerName = @Server2Name
SET @sql = ‘SELECT ”’ + @TableName + ”’, ”’ + @Server1Name + ”’, ”’ + @Server2Name + ”’, *
FROM ‘ + @DB1Name + ‘.dbo.’ + @TableName + ‘ ‘ + @Server1Name + ‘
LEFT JOIN ‘ + @DB2Name + ‘.dbo.’ + @TableName + ‘ ‘ + @Server2Name + ‘ ON ‘
SET @sqlField = ”
SET @FirstFieldName = ”
DECLARE FIELD CURSOR FOR
SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = @TableName)
OPEN FIELD
FETCH NEXT FROM FIELD INTO @FieldName, @Nullable, @Default, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT (@FieldName = ‘createdDate’) AND NOT(@FieldName = ‘updatedDate’) AND NOT(@FieldName = ‘updatedBy’)
BEGIN
IF @sqlField = ”
BEGIN
SET @sqlField = ”
SET @FirstFieldName = @FieldName
END
ELSE
BEGIN
SET @sqlField = @sqlField + ‘ AND ‘
END
IF (@Nullable = ‘Yes’) AND (@Default IS NULL) AND (@DataType like ‘%char’)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘ + ” ”,””) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘ + ” ”,””)) ‘
ELSE IF (@Nullable = ‘Yes’) AND (@Default IS NULL) AND (@DataType like ‘datetime’)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘,”1/1/1900”) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘,”1/1/1900”)) ‘
ELSE IF (@Nullable = ‘Yes’) AND (@Default IS NULL)
SET @sqlField = @sqlField + ‘(ISNULL(‘ + @Server1Name + ‘.’ + @FieldName + ‘,”0”) = ISNULL(‘ + @Server2Name + ‘.’ + @FieldName + ‘,”0”)) ‘
ELSE
SET @sqlField = @sqlField + ‘(‘ + @Server1Name + ‘.’ + @FieldName + ‘ = ‘ + @Server2Name + ‘.’ + @FieldName + ‘) ‘
END
FETCH NEXT FROM FIELD INTO @FieldName, @Nullable, @Default, @DataType
END
CLOSE FIELD
DEALLOCATE FIELD
SET @sql = @sql + @sqlField + ‘WHERE (((‘ + @Server2Name + ‘.’ + @FirstFieldName + ‘) Is Null)) ‘
PRINT @sql
IF @SQLStatements = ”
SET @SQLStatements = @sql
ELSE
SET @SQLStatements = @SQLStatements + ‘<br><br>’ + @sql
FETCH NEXT FROM TABLERUN INTO @TableName, @Server1Name, @Server2Name
END
CLOSE TABLERUN
DEALLOCATE TABLERUN
SELECT *
FROM #TABLES
WHERE ExtraCount >0
ORDER BY TableName, ServerWith, ServerWithOut
DROP TABLE #TABLES
END

Advertisements

About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s