Today, I received an email from one of my blog readers, who would like to match test and production databases. Here is a basic script to compare two databases.
Given below are the objects, script will compare in source and target databases.
- Tables
- Views
- Stored Procedures
- User Defined Functions
- Triggers
- Primary Keys
- Indexes
- Missing column in table or view
- Mismatch data type in table or view
- Missing Parameter in the stored procedure
Create Procedure Sp_Compare_two_database_Schema
@Source_DB_Name nvarchar(Max),
@Target_DB_Name varchar(Max)
As
BEGIN
DECLARE @SQL nvarchar(MAX)
SET @Source_DB_Name=QUOTENAME(@Source_DB_Name)
SET @Target_DB_Name=QUOTENAME(@Target_DB_Name)
SET @SQL=''
CREATE TABLE #Result
(
[Main Object Type] nvarchar(max),
[Main Object Name] nvarchar(max),
[Type] varchar(50),
[Sub Object Type] nvarchar(max),
[Sub Object Name] nvarchar(max)
)
--Match Main Objects Like Tables, view, Stored Procedure , triggers.
SET @SQL= 'Insert into #Result Select A.[type_desc] as [Main Object Type],A.[name],''Object Missing'' as [Type]
,A.[type_desc] as [Sub Object Type]
,A.[name] as [Sub Object Name]
from ' + @Source_DB_Name + '.sys.objects A
Where [Parent_object_id]=0 And A.[name] NOT IN(
Select [name] from ' + @Target_DB_Name + '.sys.objects)
Order By A.[type_desc]'
Print @SQL
EXEC (@SQL)
--Match Sub Objects Like Foreign Keys.
SET @SQL= 'Insert into #Result Select A.[type_desc] as [Main Object Type],A.[name],''Object Missing'' as [Type]
,B.[type_desc] as [Sub Object Type]
,B.[name] as [Sub Object Name]
from ' + @Source_DB_Name + '.sys.objects A
Inner Join ' + @Source_DB_Name + '.sys.objects B
On A.[object_id]=B.[Parent_object_id]
Where B.[name] NOT IN(
Select [name] from ' + @Target_DB_Name + '.sys.objects)
Order By A.[type_desc]'
Print @SQL
EXEC (@SQL)
--Find if any column is missing in target database.
SET @SQL= ';With CteA AS (Select A.[type_desc] as [Main Object Type],A.[Name] as [Main Object Name],''Column Missing'' as [Type],B.[Name] as [Column Name]
from ' + @Source_DB_Name + '.sys.objects A
Inner Join ' + @Source_DB_Name + '.sys.columns B
On A.[object_id] =B.[object_id]
Where A.[Type] In (''U'',''V'')
)
,CteB AS (Select A.[type_desc] as [Main Object Type],A.[Name] as [Main Object Name],''Column Missing'' as [Type],B.[Name] as [Column Name]
from ' + @Target_DB_Name + '.sys.objects A
Inner Join ' + @Target_DB_Name + '.sys.columns B
On A.[object_id] =B.[object_id]
Where A.[Type] In (''U'',''V'')
)
Insert into #Result
Select A.[Main Object Type],A.[Main Object Name],A.[Type], ''Column'' as [Sub Object Type],A.[Column Name] from CTEA A
Left Join CTEB B On
A.[Main Object Type]=B.[Main Object Type]
And A.[Main Object Name]=B.[Main Object Name]
And A.[Column Name]=B.[Column Name]
Where (B.[Main Object Name] is NULL OR B.[Column Name] is NULL)
And A.[Main Object Name] Not In (Select [Main Object Name] from #Result A
Where A.[Type]=''Object Missing'')
Order By A.[Main Object Type],A.[Main Object Name], A.[Column Name]'
Print @SQL
EXEC (@SQL)
--Find if any column data type is not sync with target database.
SET @SQL= ';With CteA AS (Select A.[type_desc] as [Main Object Type],A.[Name] as [Main Object Name]
,''Data Type Difference'' as [Type],B.[Name] as [Column Name]
,B.[system_type_id]
from ' + @Source_DB_Name + '.sys.objects A
Inner Join ' + @Source_DB_Name + '.sys.columns B
On A.[object_id] =B.[object_id]
Where A.[Type] In (''U'',''V'')
)
,CteB AS (Select A.[type_desc] as [Main Object Type],A.[Name] as [Main Object Name],''Data Type Difference'' as [Type],B.[Name] as [Column Name],B.[system_type_id]
from ' + @Target_DB_Name + '.sys.objects A
Inner Join ' + @Target_DB_Name + '.sys.columns B
On A.[object_id] =B.[object_id]
Where A.[Type] In (''U'',''V'')
)
Insert into #Result Select A.[Main Object Type],A.[Main Object Name],A.[Type], ''Column'' as [Sub Object Type], A.[Column Name] from CTEA A
Inner Join CTEB B On
A.[Main Object Type]=B.[Main Object Type]
And A.[Main Object Name]=B.[Main Object Name]
And A.[Column Name]=B.[Column Name]
Where A.[system_type_id]<>B.[system_type_id]
And A.[Main Object Name] Not In (Select [Main Object Name] from #Result A
Where A.[Type]=''Object Missing'')
Order By A.[Main Object Type],A.[Main Object Name], A.[Column Name]'
Print @SQL
EXEC (@SQL)
--Find if any parameter of the procedure is missing in target database.
SET @SQL= ';With CteA AS (Select A.[type_desc] as [Main Object Type]
,A.[Name] as [Main Object Name],''Parameter Missing'' as [Type],B.[name] as [Parameter Name]
from' + @Source_DB_Name + '.sys.objects A
Inner Join ' + @Source_DB_Name + '.sys.all_parameters B On A.[object_id] =B.[Object_id]
)
,CteB AS (
Select A.[type_desc] as [Main Object Type]
,A.[Name] as [Main Object Name],''Parameter Missing'' as [Type],B.[name] as [Parameter Name]
from ' + @Target_DB_Name + '.sys.objects A
Inner Join ' + @Target_DB_Name + '.sys.all_parameters B On A.[object_id] =B.[Object_id]
)
Insert into #Result Select A.[Main Object Type],A.[Main Object Name],A.[Type]
, ''Parameter'' as [Sub Object Type]
, A.[Parameter Name] from CTEA A
Left Join CTEB B On
A.[Main Object Type]=B.[Main Object Type]
And A.[Main Object Name]=B.[Main Object Name]
And A.[Parameter Name]=B.[Parameter Name]
Where (B.[Main Object Name] IS NULL OR B.[Parameter Name] IS NULL)
And A.[Main Object Name] Not In (Select [Main Object Name] from #Result A
Where A.[Type]=''Object Missing'')
Order By A.[Main Object Type],A.[Main Object Name], A.[Parameter Name]'
Print @SQL
EXEC (@SQL
)
Select * from #Result A Order By A.[Main Object Type] DESC, A.[Main Object Name] ASC ,[Type] DESC
END
GO
--Syntax
--Sp_Compare_two_database_Schema 'Source DatabaseName','Target DatbaseName'
--Example
Sp_Compare_two_database_Schema 'Source_DB','Target_DB'
Read Full Post »
SQL Server Portal 