Compare Definition and Data of table in 2DBs

,

This proc compares the table definition and the data in a table which is in two of your databases. Typically you may have a development database and a production database on the same server and you just want to know that the table is the same on both. (This proc will express the data differences in terms of SQL in which you need to execute to bring them in line)
Typical Call:- utl_CompareTables 'YourTable', 'YourOtherDatabase'

If the SQL expressed to bring the tables in line needs to be reversed (i.e. INSERTs become DELETEs etc.) then simply run from your other database.

No match to Redgate's SQL Compare but good for a quick look!

CREATE PROCEDURE dbo.utl_CompareTables
	@cTableName 		CHAR(30)
	,@cDatabase 		CHAR(10)
	AS
/********************************************************************************************************************************************************
Purpose: 	This program will compare the definition and contents of a table on two different databases
			
Amendment Log
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date 		Who			Comment
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15/03/01	JHAYNE		Initial Version (me@julianhaynes.freeserve.co.uk)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/
SET NOCOUNT ON

DECLARE 	
	@nRowCount			INT
	,@cSQL			VARCHAR(4000)
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Check parms OK
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--Report id database supplied is missing
IF (SELECT COUNT(*) FROM Master..SysDatabases WHERE Name = @cDatabase) = 0
	BEGIN
		SELECT 'The database ''' + RTRIM(@cDatabase) + ''' does not exist'
		RETURN
	END

--Report if table missing locally
SELECT @nRowCount = COUNT(*) FROM SysObjects WHERE Name = @cTableName
IF @nRowCount = 0 
	BEGIN 
		SELECT 'Table missing in local Database'
		RETURN
	END

--Report if table missing remotely
SELECT @cSQL = 
	'
	DECLARE @nRowCount INT
	SELECT @nRowCount = COUNT(*) FROM ' + RTRIM(@cDatabase) + '..SysObjects WHERE Name = ''' + RTRIM(@cTableName) + '''
	IF @nRowCount = 0 SELECT ''Table missing in ' + RTRIM(@cDatabase) + ' Database''
	'
	EXEC(@cSQL)
	IF @@ROWCOUNT = 1 RETURN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Compare the definition of the tables
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--Report missing columns as cannot be compared
SELECT @cSQL = 
	'
	SELECT 
		RTRIM(cSource) + '' Database has extra column: '' + RTRIM(cColName) AS ''Comparing Columns Structure...''
	FROM
		(
		SELECT 
			''Local'' AS cSource
			,S.Name AS cTableName
			,C.Name AS cColName
			,C.ColOrder
		FROM 
			SysObjects S
			JOIN SysColumns AS C ON C.ID = S.ID
			JOIN ' + RTRIM(@cDatabase) + '..SysObjects REMS ON REMS.Name = S.Name
			LEFT OUTER JOIN  ' + RTRIM(@cDatabase) + '..SysColumns AS REMC ON REMC.ID = REMS.ID AND REMC.Name = C.Name
		WHERE 
			S.Name = ''' + RTRIM(@cTableName) + '''
			AND REMC.ID IS NULL
		UNION
		SELECT 
			''' + RTRIM(@cDatabase) + '''
			,S.Name
			,REMC.Name
			,REMC.ColOrder
		FROM 
			SysObjects S
			JOIN  ' + RTRIM(@cDatabase) + '..SysObjects REMS ON REMS.Name = S.Name
			JOIN  ' + RTRIM(@cDatabase) + '..SysColumns AS REMC ON REMC.ID = REMS.ID
			LEFT OUTER JOIN SysColumns AS C ON C.ID = S.ID AND C.Name = REMC.Name
		WHERE 
			S.Name = ''' + RTRIM(@cTableName) + '''
			AND C.ID IS NULL
		) LR
	ORDER BY
		LR.cColName
	'
EXEC (@csql)

--Report datatype differences as may make comparison fall over
SELECT @cSQL = 
	'
	SELECT 
		''' + RTRIM(@cDatabase) + ' DATATYPE: ''
			+ RTRIM(REMT.Name)
			+ 
			CASE 
				WHEN REMT.Name = ''int'' OR REMT.Name = ''smallint'' OR REMT.Name = ''tinyint'' OR REMT.Name = ''datetime''
					THEN '''' 
				WHEN REMT.Name = ''char'' OR REMT.Name = ''varchar''
					THEN ''('' + RTRIM(CONVERT(CHAR(10), REMC.Length)) + '')'' 
				WHEN REMT.Name = ''nvarchar''
					THEN ''('' + RTRIM(CONVERT(CHAR(10), REMC.Length/2)) + '')'' 
				WHEN REMT.Name = ''decimal'' OR REMT.Name = ''numeric''
					THEN ''('' + RTRIM(CONVERT(CHAR(30), REMC.XPrec)) + '','' + RTRIM(CONVERT(CHAR(10), REMC.XScale)) + '')'' 
				ELSE ''(?)'' 
			END
			+ CASE WHEN REMC.IsNullable = 0 THEN '' Not Null'' ELSE '''' END
			+ CASE WHEN REMC.AutoVal IS NULL THEN '''' ELSE '' Identity'' END
			+ '' Local DATATYPE: ''
			+ RTRIM(T.Name)
			+ 
			CASE 
				WHEN T.Name = ''int'' OR T.Name = ''smallint'' OR T.Name = ''tinyint'' OR T.Name = ''datetime''
					THEN '''' 
				WHEN T.Name = ''char'' OR T.Name = ''varchar''
					THEN ''('' + RTRIM(CONVERT(CHAR(10), C.Length)) + '')'' 
				WHEN T.Name = ''nvarchar''
					THEN ''('' + RTRIM(CONVERT(CHAR(10), C.Length/2)) + '')'' 
				WHEN T.Name = ''decimal'' OR T.Name = ''numeric''
					THEN ''('' + RTRIM(CONVERT(CHAR(30), C.XPrec)) + '','' + RTRIM(CONVERT(CHAR(10), C.XScale)) + '')'' 
				ELSE ''(?)'' 
			END
			+ CASE WHEN C.IsNullable = 0 THEN '' Not Null'' ELSE '''' END
			+ CASE WHEN C.AutoVal IS NULL THEN '''' ELSE '' Identity'' END
			+ '' for ''
			+ RTRIM(C.Name)  AS ''Comparing Columns Definition...''
	FROM 
		SysObjects S
		JOIN ' + RTRIM(@cDatabase) + '..SysObjects REMS ON REMS.Name = S.Name
		JOIN SysColumns AS C ON C.ID = S.ID
		JOIN ' + RTRIM(@cDatabase) + '..SysColumns AS REMC ON REMC.ID = REMS.ID AND REMC.Name = C.Name
		JOIN SysTypes AS T ON T.XUserType = C.XUserType
		JOIN ' + RTRIM(@cDatabase) + '..SysTypes AS REMT ON REMT.XUserType = REMC.XUserType
	WHERE 
		S.Name = ''' + RTRIM(@cTableName) + '''
		AND (
			T.Name <> REMT.Name 
			OR C.Length <> REMC.Length 
			OR C.XPrec <> REMC.XPrec
			OR C.XScale <> REMC.XScale
			OR C.IsNullable <> REMC.IsNullable
			OR (CASE WHEN C.AutoVal IS NULL THEN ''N'' ELSE ''Y'' END) <> (CASE WHEN REMC.AutoVal IS NULL THEN ''N'' ELSE ''Y'' END)
		)
	ORDER BY
		S.Name
		,C.Name
	'
EXEC (@cSQL)

--Report default differences in case user interested
SELECT @cSQL = 
	'
	SELECT 
		''COLUMN: '' + RTRIM(C.Name)
			+ '' ' + RTRIM(@cDatabase) + ' DEFAULT: '' + RTRIM(ISNULL(REMTX.Text,''()''))
			+ '' Local DEFAULT: '' + RTRIM(ISNULL(TX.Text,''()'')) AS ''Comparing Columns Default...''
	FROM
		SysObjects T
		JOIN SysColumns C ON C.ID = T.ID
		JOIN ' + RTRIM(@cDatabase) + '..SysObjects REMT ON REMT.Name = T.Name
		JOIN ' + RTRIM(@cDatabase) + '..SysColumns REMC ON REMC.ID = REMT.ID AND REMC.Name = C.Name
		LEFT OUTER JOIN SysComments TX ON TX.ID = C.cDefault
		LEFT OUTER JOIN ' + RTRIM(@cDatabase) + '..SysComments REMTX ON REMTX.ID = REMC.cDefault
	WHERE
		ISNULL(TX.Text, '''') <> ISNULL(REMTX.Text, '''')
		AND T.Name = ''' + RTRIM(@cTableName) + '''
	ORDER BY
		C.Name
	'
EXEC (@cSQL)

--Output Local fields for order analysis
declare @isapk char(1)
DECLARE Columns CURSOR FOR
SELECT 
	C.Name cName
	,ST.Name cType
	,CASE WHEN C.AutoVal IS NULL THEN 'N' ELSE 'Y' END cIdentity
	,CASE WHEN SK.ColID IS NULL THEN 'N' ELSE 'Y' END cPK
FROM
	SysObjects T 
	JOIN SysColumns C ON C.ID = T.ID
	JOIN SysTypes ST ON ST.XUserType = C.XUserType
	LEFT OUTER JOIN SysIndexes SI ON SI.ID = T.ID AND (SI.Status = 2050 or SI.Status = 18450)
	LEFT OUTER JOIN SysIndexKeys SK on SK.id = T.ID AND SK.IndID = SI.IndID AND SK.ColID = C.ColID
WHERE
	T.Name = @cTableName
ORDER BY
	C.ColOrder
OPEN Columns
DECLARE @cFields VARCHAR(4000), @cFieldsForIDInsert VARCHAR(4000), @cColName CHAR(30), @cType CHAR(30), @cIdentity CHAR(1), @cPK CHAR(1)
SELECT @cFields = ''
WHILE 1=1
	BEGIN
		FETCH NEXT FROM Columns INTO @cColName, @cType, @cIdentity, @cPK

		--*** Exit loop if last row reached
		IF @@FETCH_STATUS  = -1 BREAK
		
		IF @cpk = 'Y' select @isapk = 'Y'
		SELECT @cFields = RTRIM(@cFields) + ', ' + RTRIM(@cColName) + CASE WHEN @cPK = 'Y' THEN '(PK)' ELSE '' END
	END
SELECT SUBSTRING(@cFields, 3, 255) AS 'Order of Local columns...'
SELECT @cFieldsForIDInsert = '(' + RTRIM(SUBSTRING(REPLACE(@cFields, '(PK)', ''),3,255)) + ')'
CLOSE Columns
DEALLOCATE Columns

--Output Remote fields for order analysis
SELECT @cSQL = 
	'
	DECLARE Columns CURSOR FOR
	SELECT 
		C.Name cName
		,ST.Name cType
		,CASE WHEN C.AutoVal IS NULL THEN ''N'' ELSE ''Y'' END cIdentity
		,CASE WHEN SK.ColID IS NULL THEN ''N'' ELSE ''Y'' END cPK
	FROM
		' + RTRIM(@cDatabase) + '..SysObjects T 
		JOIN ' + RTRIM(@cDatabase) + '..SysColumns C ON C.ID = T.ID
		JOIN ' + RTRIM(@cDatabase) + '..SysTypes ST ON ST.XUserType = C.XUserType
		LEFT OUTER JOIN ' + RTRIM(@cDatabase) + '..SysIndexes SI ON SI.ID = T.ID AND (SI.Status = 2050 or SI.Status = 18450)
		LEFT OUTER JOIN ' + RTRIM(@cDatabase) + '..SysIndexKeys SK on SK.id = T.ID AND SK.IndID = SI.IndID AND SK.ColID = C.ColID
	WHERE
		T.Name = ''' + RTRIM(@cTableName) + '''
	ORDER BY
		C.ColOrder
	OPEN Columns
	DECLARE @cFields VARCHAR(4000), @cColName CHAR(30), @cType CHAR(30), @cIdentity CHAR(1), @cPK CHAR(1)
	SELECT @cFields = ''''
	WHILE 1=1
		BEGIN
			FETCH NEXT FROM Columns INTO @cColName, @cType, @cIdentity, @cPK	
			IF @@FETCH_STATUS  = -1 BREAK
			SELECT @cFields = RTRIM(@cFields) + '', '' + RTRIM(@cColName) + CASE WHEN @cPK = ''Y'' THEN ''(PK)'' ELSE '''' END
		END
	SELECT SUBSTRING(@cFields, 3, 255) AS ''Order of ' + RTRIM(@cDatabase) + ' columns...''
	CLOSE Columns
	DEALLOCATE Columns
	'
EXEC (@cSQL)

--Check if has a PK
IF @isapk is null
	begin
		SELECT 'There is no primary key. Therefore data comparison will not be made'
		RETURN
	end

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Compare actual data
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Loop through fields
DECLARE Columns CURSOR FOR
SELECT 
	C.Name cName
	,ST.Name cType
	,CASE WHEN C.AutoVal IS NULL THEN 'N' ELSE 'Y' END cIdentity
	,CASE WHEN SK.ColID IS NULL THEN 'N' ELSE 'Y' END cPK
FROM
	SysObjects T 
	JOIN SysColumns C ON C.ID = T.ID
	JOIN SysTypes ST ON ST.XUserType = C.XUserType
	LEFT OUTER JOIN SysIndexes SI ON SI.ID = T.ID AND (SI.Status = 2050 or SI.Status = 18450)
	LEFT OUTER JOIN SysIndexKeys SK on SK.id = T.ID AND SK.IndID = SI.IndID AND SK.ColID = C.ColID
WHERE
	T.Name = @cTableName
ORDER BY
	4 DESC
	,SK.KeyNo ASC
	,C.ColOrder
OPEN Columns
DECLARE @cJoin VARCHAR(255)
DECLARE @cWhere VARCHAR(2000)
DECLARE @cFirstPK CHAR(50)
DECLARE @cField CHAR(255)
DECLARE @cFieldRemote CHAR(255)
declare @cIsIdentity CHAR(1)
SELECT @cField = ''
SELECT @cFieldRemote = ''
SELECT @cFields = ''
SELECT @cJoin = ''
SELECT @cWhere = ''
SELECT @cFirstPK = ''
WHILE 1=1
	BEGIN
		FETCH NEXT FROM Columns INTO @cColName, @cType, @cIdentity, @cPK

		--*** Exit loop if last row reached
		IF @@FETCH_STATUS  = -1 BREAK
		
		SELECT @cField =
			CASE 
				WHEN RTRIM(@cType) = 'char' OR RTRIM(@cType) = 'varchar' OR RTRIM(@cType) = 'nvarchar' OR RTRIM(@cType) = 'text'
					THEN ' + '', '' + CASE WHEN L.' + RTRIM(@cColName) + ' IS NULL THEN ''NULL'' ELSE '''''''' + RTRIM(L.' + RTRIM(@cColName) + ') + '''''''' END '
				WHEN RTRIM(@cType) = 'int' OR RTRIM(@cType) = 'smallint' OR RTRIM(@cType) = 'tinyint' OR RTRIM(@cType) = 'decimal' OR RTRIM(@cType) = 'numeric' 
					THEN ' + '', '' + ISNULL(RTRIM(CONVERT(CHAR(30), L.' + RTRIM(@cColName) + ')), ''NULL'')'
				WHEN RTRIM(@cType) = 'datetime'
					THEN ' + '', '' + CASE WHEN L.' + RTRIM(@cColName) + ' IS NULL THEN ''NULL'' ELSE '''''''' + CONVERT(CHAR(26), L.' + RTRIM(@cColName) + ',109) + '''''''' END '
				ELSE '?' 
			END
		SELECT @cFields = RTRIM(@cFields) + RTRIM(@cField)
		IF @cPK = 'Y'  
			BEGIN
				SELECT @cJoin = RTRIM(@cJoin) + ' AND L.' + RTRIM(@cColName) + ' = R.' + RTRIM(@cColName)
				IF @cFirstPK = '' 
					BEGIN
						SELECT @cFirstPK = @cColName
						SELECT @cWhere = RTRIM(@cWhere) + '+ ''' + RTRIM(@ccolName) + ' = ''' + RTRIM(SUBSTRING(@cField, 8, 255))
					END
				ELSE
						SELECT @cWhere = RTRIM(@cWhere) + ' + '' AND ' + RTRIM(@ccolName) + ' = ''' + RTRIM(SUBSTRING(@cField, 8, 255))
			END
		IF @cIdentity = 'Y' SELECT @cIsIdentity = 'Y'
	END

CLOSE Columns
DEALLOCATE Columns
SELECT @cFields = SUBSTRING(@cFields,9,4000)
SELECT @cJoin = SUBSTRING(@cJoin,6,255)

--DELETEs required to bring remote inline with local (Aliases reversed)
SELECT @cSQL = '
	SELECT ''DELETE FROM ' + RTRIM(@cDatabase) + '..' + RTRIM(@cTableName) + ' WHERE ''' + RTRIM(@cWhere) + ' AS ''--Rows to delete...''
	FROM
		' + RTRIM(@cDatabase) + '..' + RTRIM(@cTableName) + ' L
		LEFT OUTER JOIN '  + RTRIM(@cTableName) + ' R ON ' + RTRIM(@cJoin)  + '
	WHERE
		R.' + RTRIM(@cFirstPK) + ' IS NULL'
EXEC (@cSQL)

--INSERTs required to bring remote inline with local
IF @cIsIdentity = 'Y' SELECT 'SET IDENTITY_INSERT ' + RTRIM(@cDatabase) + '..' + RTRIM(@cTableName) + ' ON'
SELECT @cSQL = '
	SELECT ''INSERT ' + RTRIM(@cDatabase) + '..' + RTRIM(@cTableName) + CASE WHEN @cIsIdentity = 'Y' THEN ' ' + RTRIM(@cFieldsForIDInsert) ELSE '' END + ' SELECT ''' + RTRIM(@cFields) + ' AS ''--Rows to insert...''
	FROM
		' + RTRIM(@cTableName) + ' L
		LEFT OUTER JOIN ' +  RTRIM(@cDatabase) + '..' + RTRIM(@cTableName) + ' R ON ' + RTRIM(@cJoin)  + '
	WHERE
		R.' + RTRIM(@cFirstPK) + ' IS NULL'
EXEC (@cSQL)
IF @cIsIdentity = 'Y' SELECT 'SET IDENTITY_INSERT ' + RTRIM(@cDatabase) + '..' + RTRIM(@cTableName) + ' OFF'

--Cursor through all rows and suggest SQL for changes...

--Loop through non PK fields
DECLARE Columns CURSOR FOR
SELECT 
	C.Name cName
	,ST.Name cType
FROM
	SysObjects T 
	JOIN SysColumns C ON C.ID = T.ID
	JOIN SysTypes ST ON ST.XUserType = C.XUserType

	LEFT OUTER JOIN SysIndexes SI ON SI.ID = T.ID AND (SI.Status = 2050 or SI.Status = 18450)
	LEFT OUTER JOIN SysIndexKeys SK on SK.id = T.ID AND SK.IndID = SI.IndID AND SK.ColID = C.ColID
WHERE
	T.Name = @cTableName
	AND SK.ColID IS NULL
ORDER BY
	C.ColOrder
DECLARE @cNull CHAR(50)
OPEN Columns
WHILE 1=1
	BEGIN
		FETCH NEXT FROM Columns INTO @cColName, @cType

		--*** Exit loop if last row reached
		IF @@FETCH_STATUS  = -1 BREAK
		

		SELECT @cField =
			CASE 
				WHEN RTRIM(@cType) = 'char' OR RTRIM(@cType) = 'varchar' OR RTRIM(@cType) = 'nvarchar' OR RTRIM(@cType) = 'text'
					THEN ' + CASE WHEN L.' + RTRIM(@cColName) + ' IS NULL THEN ''NULL'' ELSE '''''''' + RTRIM(L.' + RTRIM(@cColName) + ') + '''''''' END '
				WHEN RTRIM(@cType) = 'int' OR RTRIM(@cType) = 'smallint' OR RTRIM(@cType) = 'tinyint' OR RTRIM(@cType) = 'decimal' OR RTRIM(@cType) = 'numeric' 
					THEN ' + ISNULL(RTRIM(CONVERT(CHAR(30), L.' + RTRIM(@cColName) + ')), ''NULL'')'
				WHEN RTRIM(@cType) = 'datetime'
					THEN ' + CASE WHEN L.' + RTRIM(@cColName) + ' IS NULL THEN ''NULL'' ELSE '''''''' + CONVERT(CHAR(26), L.' + RTRIM(@cColName) + ',109) + '''''''' END '
				ELSE '?' 
			END

		SELECT @cFieldRemote =
			CASE 
				WHEN RTRIM(@cType) = 'char' OR RTRIM(@cType) = 'varchar' OR RTRIM(@cType) = 'nvarchar' OR RTRIM(@cType) = 'text'
					THEN ' + CASE WHEN R.' + RTRIM(@cColName) + ' IS NULL THEN ''NULL'' ELSE '''''''' + RTRIM(R.' + RTRIM(@cColName) + ') + '''''''' END '
				WHEN RTRIM(@cType) = 'int' OR RTRIM(@cType) = 'smallint' OR RTRIM(@cType) = 'tinyint' OR RTRIM(@cType) = 'decimal' OR RTRIM(@cType) = 'numeric' 
					THEN ' + ISNULL(RTRIM(CONVERT(CHAR(30), R.' + RTRIM(@cColName) + ')), ''NULL'')'
				WHEN RTRIM(@cType) = 'datetime'
					THEN ' + CASE WHEN R.' + RTRIM(@cColName) + ' IS NULL THEN ''NULL'' ELSE '''''''' + CONVERT(CHAR(26), R.' + RTRIM(@cColName) + ',109) + '''''''' END '
				ELSE '?' 
			END

		SELECT @cNull =
			CASE 
				WHEN RTRIM(@cType) = 'char' OR RTRIM(@cType) = 'varchar' OR RTRIM(@cType) = 'nvarchar' OR RTRIM(@cType) = 'text'
					THEN ''''''
				WHEN RTRIM(@cType) = 'int' OR RTRIM(@cType) = 'smallint' OR RTRIM(@cType) = 'tinyint' OR RTRIM(@cType) = 'decimal' OR RTRIM(@cType) = 'numeric' 
					THEN '0'
				WHEN RTRIM(@cType) = 'datetime'
					THEN ''' 01 Jan 1900 00:00:00'''
				ELSE '?' 
			END


		--Now create all update statements where rows for column differ...
		SELECT @cSQL = 
			'
			SELECT 
				''UPDATE ' + RTRIM(@cDatabase) + '..' + RTRIM(@cTableName) + ' SET ' + RTRIM(@cColName) + ' = ''' + RTRIM(@cField) + ' + '' WHERE '' + ' + RTRIM(@cWhere) + ' + '' --Remote data was:''' + RTRIM(@cFieldRemote) + ' AS ''--Comparing ' + RTRIM(@cColName) + ' data...''
			FROM
				' + RTRIM(@cTableName) + ' L
				JOIN ' + RTRIM(@cDatabase) + '..' + RTRIM(@cTableName) + ' R ON ' + RTRIM(@cJoin) + '
			WHERE
				ISNULL(L.' + RTRIM(@cColName) + ',' + RTRIM(@cNull) + ') <> ISNULL(R.' + RTRIM(@cColName) + ', ' + RTRIM(@cNull) + ')
			'
		EXEC (@cSQL)	

	END

CLOSE Columns
DEALLOCATE Columns


SET NOCOUNT OFF

Rate

Share

Share

Rate