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
