Compare SQL Database By Using SQL Query

Compare SQL Source & Target Databases Objects 

To compare SQL DB objects we use Dynamic views Like  "SYS.SYSOBJECTS, "SYS.SYSCOMMENTS", "INFORMATION_SCHEMA.COLUMNS"

STEP 1

Declare local varible 

DECLARE @SourceDbName SYSNAME = 'SqlBank'; -- Source Database Name
DECLARE @TargetDbName SYSNAME = 'SqlBankCore';-- Target Databse Name

DECLARE @SYSobjectCommonQuery NVARCHAR(4000);
DECLARE @SYSCommentsQuery NVARCHAR(4000);

DECLARE @SourceSYSObjectsExecute NVARCHAR(4000);
DECLARE @SourceSYSCommentExecute NVARCHAR(4000);

DECLARE @TargetSYSObjectsExecute NVARCHAR(4000);
DECLARE @TargetSYSCommentExecute NVARCHAR(4000);

DECLARE @InformationCommonQuery NVARCHAR(4000);
DECLARE @ExecuteSourceInformationColumn NVARCHAR(4000);

STEP 2

Declare Variable table for Objects xtypes 

DECLARE @Tbl_Xtypes TABLE(xtype CHAR(2),xtypeDefination VARCHAR(1000))

INSERT INTO @Tbl_Xtypes Values
('AF','Aggregate function (CLR)'),('C ','CHECK Constraint'),
('D','Default or DEFAULT Constraint'),('F','FOREIGN KEY Constraint'),
('FN','Scalar Function'),('FS','Assembly (CLR) Scalar-Function'),
('FT','Assembly (CLR) Table-Valued Function'),('IF','In-lined Table Function'),
('IT','Internal Table'),('L','Log'),('P','Stored Procedure'),
('PC','Assembly (CLR) Stored Procedure'),
('PK','PRIMARY KEY Constraint (Type is K)'),
('RF','Replication Filter Stored Procedure'),('S','System Table'),
('SN','Synonym'),('SQ','Service Queue'),
('TA','Assembly (CLR) DML Trigger'),('TF','Table Function'),
('TR','SQL DML Trigger'),('TT','Table Type'),
('U','User Table'),('UQ','UNIQUE Constraint'),
('V','View') ,('X','Extended Stored Procedure') 

STEP 3

Assign Common Query for local object

SET @SYSobjectCommonQuery=N'
SELECT NAME,ID,XTYPE,UID,INFO,STATUS,BASE_SCHEMA_VER,REPLINFO,PARENT_OBJ
,CRDATE,FTCATID,SCHEMA_VER,STATS_SCHEMA_VER,TYPE,USERSTAT,SYSSTAT,INDEXDEL,
REFDATE,VERSION,DELTRIG,INSTRIG,UPDTRIG,SELTRIG,CATEGORY,CACHE
FROM SYS.SYSOBJECTS WHERE XTYPE NOT IN (''S'') ORDER BY NAME ASC';

SET @SYSCommentsQuery =N'SELECT id,number,colid,status,ctext,texttype,
language,encrypted,compressed,text FROM sys.syscomments';

STEP 4

SET Source & Target Database Common query to get DB objects

SET @SourceSYSObjectsExecute=N'USE ['+@SourceDbName+'] '+@SYSobjectCommonQuery+'';
SET @SourceSYSCommentExecute=N'USE ['+@SourceDbName+'] '+@SYSCommentsQuery+'';
SET @TargetSYSObjectsExecute=N'USE ['+@TargetDbName+'] '+@SYSobjectCommonQuery+'';
SET @TargetSYSCommentExecute=N'USE ['+@TargetDbName+'] '+@SYSCommentsQuery+'';

STEP 5

Create Temp Table to insert SYS.SYSObjects Objects to Temp Table

IF(OBJECT_ID('tempdb..#Tbl_SourceObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceObjects
END

CREATE TABLE #Tbl_SourceObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_SourceObjects
EXEC sp_executesql @SourceSYSObjectsExecute

STEP 6

Create Temp Table to insert SYS.SYSComments Objects to Temp Table

IF(OBJECT_ID('tempdb..#Tbl_SourceComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceComments
END

CREATE TABLE #Tbl_SourceComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

INSERT INTO #Tbl_SourceComments
EXEC sp_executesql @SourceSYSCommentExecute

STEP 7

IF(OBJECT_ID('tempdb..#Tbl_TargetObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetObjects
END

CREATE TABLE #Tbl_TargetObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_TargetObjects
EXEC sp_executesql @TargetSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_TargetComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetComments
END

CREATE TABLE #Tbl_TargetComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_TargetComments
EXEC sp_executesql @TargetSYSCommentExecute

--======================================================================

Select @SourceDbName [Source DataBase Name], Main.name [Source Object Name],
@TargetDbName[Target DataBase Name],Main.[Object Defination],SubMain.name [Target Object Name]
,Main.text[Source Object text],SubMain.text [Target Object text],
CASE WHEN Main.text=SubMain.text Then 
 Concat(Main.[Object Defination], '  Object Match with Source Databse')
 else
 Concat(Main.[Object Defination], ' Object Mismatch Or Not Found with Source Database')
 end [Global Message]
from
(
SELECT o.name, cs.id,cs.number,cs.colid,cs.status,cs.ctext,cs.texttype,
cs.language,cs.encrypted,cs.compressed,ISNULL(cs.text,'') text,
CASE WHEN o.xtype in (SELECT x.xtype from @Tbl_Xtypes x)
THEN (Select xx.xtypeDefination from @Tbl_Xtypes xx where xx.xtype=o.xtype)
else '' end [Object Defination]
FROM #Tbl_SourceComments cs join
#Tbl_SourceObjects o on o.id=cs.id
) Main
left join
(
select css.id,oo.name,ISNULL(css.text,'') text,oo.xtype FROM
#Tbl_TargetComments css  left join
#Tbl_TargetObjects oo on css.id=oo.id

)SubMain  on Main.name=SubMain.name
SET @InformationCommonQuery = N'
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX
,NUMERIC_SCALE,DATETIME_PRECISION,ISNULL(CHARACTER_SET_CATALOG,'''') 
CHARACTER_SET_CATALOG
,ISNULL(CHARACTER_SET_SCHEMA,'''') CHARACTER_SET_SCHEMA,
ISNULL(CHARACTER_SET_NAME,'''')
CHARACTER_SET_NAME,
ISNULL(COLLATION_CATALOG,'''') COLLATION_CATALOG
,ISNULL(COLLATION_SCHEMA,'''') COLLATION_SCHEMA,ISNULL(COLLATION_NAME,'''')
COLLATION_NAME,ISNULL(DOMAIN_CATALOG,'''') DOMAIN_CATALOG,ISNULL(DOMAIN_SCHEMA,'''')
DOMAIN_SCHEMA
,ISNULL(DOMAIN_NAME,'''') DOMAIN_NAME from INFORMATION_SCHEMA.COLUMNS';

SET @ExecuteSourceInformationColumn = N'USE ['+@SourceDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_SourceInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_SourceInfoTable
END

CREATE TABLE #Tbl_SourceInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_SourceInfoTable
exec sp_executesql @ExecuteSourceInformationColumn

DECLARE @ExecuteTargetInformationColumn NVARCHAR(4000);

SET @ExecuteTargetInformationColumn = N'USE ['+@TargetDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_TargetInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_TargetInfoTable
END

CREATE TABLE #Tbl_TargetInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_TargetInfoTable
EXEC sp_executesql @ExecuteTargetInformationColumn

SELECT s.TABLE_NAME [Source Table Name],s.COLUMN_NAME [Source Col Name],
s.DATA_TYPE [Source Col Data Type],
IC.TABLE_NAME [Target Table Name],
IC.COLUMN_NAME [Target Col Name],IC.DATA_TYPE [Target Col Data Type] ,
CASE WHEN s.COLUMN_NAME= IC.COLUMN_NAME
THEN 'Match'
ELse 'Column Mismatch Or Not Found' 
end [Global Message Info]
FROM #Tbl_SourceInfoTable s LEFT JOIN
     #Tbl_TargetInfoTable IC 
			  ON s.COLUMN_NAME=IC.COLUMN_NAME
			  AND s.TABLE_NAME=IC.TABLE_NAME

Combine Script

DECLARE @SourceDbName SYSNAME = 'SqlBank';
DECLARE @TargetDbName SYSNAME = 'SqlBankCore';

DECLARE @SYSobjectCommonQuery NVARCHAR(4000);
DECLARE @SYSCommentsQuery NVARCHAR(4000);

DECLARE @SourceSYSObjectsExecute NVARCHAR(4000);
DECLARE @SourceSYSCommentExecute NVARCHAR(4000);

DECLARE @TargetSYSObjectsExecute NVARCHAR(4000);
DECLARE @TargetSYSCommentExecute NVARCHAR(4000);

DECLARE @InformationCommonQuery NVARCHAR(4000);
DECLARE @ExecuteSourceInformationColumn NVARCHAR(4000);

--=======Below variable table for xtype & xtype defination list--=====
DECLARE @Tbl_Xtypes TABLE(xtype CHAR(2),xtypeDefination VARCHAR(1000))

INSERT INTO @Tbl_Xtypes Values
('AF','Aggregate function (CLR)'),('C ','CHECK Constraint'),
('D','Default or DEFAULT Constraint'),('F','FOREIGN KEY Constraint'),
('FN','Scalar Function'),('FS','Assembly (CLR) Scalar-Function'),
('FT','Assembly (CLR) Table-Valued Function'),('IF','In-lined Table Function'),
('IT','Internal Table'),('L','Log'),('P','Stored Procedure'),
('PC','Assembly (CLR) Stored Procedure'),
('PK','PRIMARY KEY Constraint (Type is K)'),
('RF','Replication Filter Stored Procedure'),('S','System Table'),
('SN','Synonym'),('SQ','Service Queue'),
('TA','Assembly (CLR) DML Trigger'),('TF','Table Function'),
('TR','SQL DML Trigger'),('TT','Table Type'),
('U','User Table'),('UQ','UNIQUE Constraint'),
('V','View') ,('X','Extended Stored Procedure') 

SET @SYSobjectCommonQuery=N'
SELECT NAME,ID,XTYPE,UID,INFO,STATUS,BASE_SCHEMA_VER,REPLINFO,PARENT_OBJ
,CRDATE,FTCATID,SCHEMA_VER,STATS_SCHEMA_VER,TYPE,USERSTAT,SYSSTAT,INDEXDEL,
REFDATE,VERSION,DELTRIG,INSTRIG,UPDTRIG,SELTRIG,CATEGORY,CACHE
FROM SYS.SYSOBJECTS WHERE XTYPE NOT IN (''S'') ORDER BY NAME ASC';

SET @SYSCommentsQuery =N'SELECT id,number,colid,status,ctext,texttype,
language,encrypted,compressed,text FROM sys.syscomments';

--=============Common_Query_For_Both_Database--=====================
SET @SourceSYSObjectsExecute=N'USE ['+@SourceDbName+'] '+@SYSobjectCommonQuery+'';
SET @SourceSYSCommentExecute=N'USE ['+@SourceDbName+'] '+@SYSCommentsQuery+'';
SET @TargetSYSObjectsExecute=N'USE ['+@TargetDbName+'] '+@SYSobjectCommonQuery+'';
SET @TargetSYSCommentExecute=N'USE ['+@TargetDbName+'] '+@SYSCommentsQuery+'';

--PRINT @TargetSYSCommentExecute

IF(OBJECT_ID('tempdb..#Tbl_SourceObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceObjects
END

CREATE TABLE #Tbl_SourceObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_SourceObjects
EXEC sp_executesql @SourceSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_SourceComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceComments
END

CREATE TABLE #Tbl_SourceComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_SourceComments
EXEC sp_executesql @SourceSYSCommentExecute

--======================================================================

IF(OBJECT_ID('tempdb..#Tbl_TargetObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetObjects
END

CREATE TABLE #Tbl_TargetObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_TargetObjects
EXEC sp_executesql @TargetSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_TargetComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetComments
END

CREATE TABLE #Tbl_TargetComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_TargetComments
EXEC sp_executesql @TargetSYSCommentExecute

--======================================================================

Select @SourceDbName [Source DataBase Name], Main.name [Source Object Name],
@TargetDbName[Target DataBase Name],Main.[Object Defination],SubMain.name [Target Object Name]
,Main.text[Source Object text],SubMain.text [Target Object text],
CASE WHEN Main.text=SubMain.text Then 
 Concat(Main.[Object Defination], '  Object Match with Source Databse')
 else
 Concat(Main.[Object Defination], ' Object Mismatch Or Not Found with Source Database')
 end [Global Message]
from
(
SELECT o.name, cs.id,cs.number,cs.colid,cs.status,cs.ctext,cs.texttype,
cs.language,cs.encrypted,cs.compressed,ISNULL(cs.text,'') text,
CASE WHEN o.xtype in (SELECT x.xtype from @Tbl_Xtypes x)
THEN (Select xx.xtypeDefination from @Tbl_Xtypes xx where xx.xtype=o.xtype)
else '' end [Object Defination]
FROM #Tbl_SourceComments cs join
#Tbl_SourceObjects o on o.id=cs.id
) Main
left join
(
select css.id,oo.name,ISNULL(css.text,'') text,oo.xtype FROM
#Tbl_TargetComments css  left join
#Tbl_TargetObjects oo on css.id=oo.id

)SubMain  on Main.name=SubMain.name

--===========================Compair Tables Only--=================

SET @InformationCommonQuery = N'
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX
,NUMERIC_SCALE,DATETIME_PRECISION,ISNULL(CHARACTER_SET_CATALOG,'''') 
CHARACTER_SET_CATALOG
,ISNULL(CHARACTER_SET_SCHEMA,'''') CHARACTER_SET_SCHEMA,
ISNULL(CHARACTER_SET_NAME,'''')
CHARACTER_SET_NAME,
ISNULL(COLLATION_CATALOG,'''') COLLATION_CATALOG
,ISNULL(COLLATION_SCHEMA,'''') COLLATION_SCHEMA,ISNULL(COLLATION_NAME,'''')
COLLATION_NAME,ISNULL(DOMAIN_CATALOG,'''') DOMAIN_CATALOG,ISNULL(DOMAIN_SCHEMA,'''')
DOMAIN_SCHEMA
,ISNULL(DOMAIN_NAME,'''') DOMAIN_NAME from INFORMATION_SCHEMA.COLUMNS';

SET @ExecuteSourceInformationColumn = N'USE ['+@SourceDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_SourceInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_SourceInfoTable
END

CREATE TABLE #Tbl_SourceInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_SourceInfoTable
exec sp_executesql @ExecuteSourceInformationColumn

DECLARE @ExecuteTargetInformationColumn NVARCHAR(4000);

SET @ExecuteTargetInformationColumn = N'USE ['+@TargetDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_TargetInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_TargetInfoTable
END

CREATE TABLE #Tbl_TargetInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_TargetInfoTable
EXEC sp_executesql @ExecuteTargetInformationColumn

SELECT s.TABLE_NAME [Source Table Name],s.COLUMN_NAME [Source Col Name],
s.DATA_TYPE [Source Col Data Type],
IC.TABLE_NAME [Target Table Name],
IC.COLUMN_NAME [Target Col Name],IC.DATA_TYPE [Target Col Data Type] ,
CASE WHEN s.COLUMN_NAME= IC.COLUMN_NAME
THEN 'Match'
ELse 'Column Mismatch Or Not Found' 
end [Global Message Info]
FROM #Tbl_SourceInfoTable s LEFT JOIN
     #Tbl_TargetInfoTable IC 
			  ON s.COLUMN_NAME=IC.COLUMN_NAME
			  AND s.TABLE_NAME=IC.TABLE_NAME

Sample Output

Compare SQL Source & Target Databases Objects

Compare SQL Source & Target Databases Objects

We also provide optimize Db compare Script if anyone required script, comment below

Thank you for Reading