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
HostForLIFEASP.NET SQL Server 2019 Hosting
