Find Mismatched Views and Stored Procs
Hi,
Sometimes during development, stored procs and views are modified or added. These additions dont always follow through to the staging environment, causing the Application to crash or recover gracefully.
I use this stored procs to find these mismatches and missing objects. On my SQL Box this stored proc has been created in the master database, but it could be created in any database. Execution is simple it is:
exec sp_utl_FindMisMatchedObjects 'FirstdatabaseName','SecondDatabasename'
Thought I would share this code in thanks for the many times I have been helped out on this site.
Pratap
Note: Due to the tabs I use during development I find that copying and pasting from sqlserverCentral web page to QueryAnalyzer gives a number of errors, instead paste to Notepad, replace the control characters with spaces using ctrl+h and then paste to Query Analyzer.
Create proc [dbo].[sp_utl_FindMisMatchedObjects]
( @strDbName1 varchar(20)=null
,@strDbName2 varchar(20)=null
)
as
BEGIN
/*
Author : Pratap J Prabhu
Date : 1/10/2008
Description : - Check consistency of views and
stored procs between 2 databases.
- Used to find missing and mismatched
stored procs and views between 2
databases
Usage : exec master.dbo.sp_utl_FindMisMatchedObjects 'DevDB', 'StageDB'
*/ set nocount on
declare @iRetVal int
set @iRetVal=0
if(@strDbName1 is null or @strDbname2 is null)
begin
print 'Syntax : exc sp_utl_FindMisMatchedObjects ''DBName'',''DBName2'''
set @iRetVal=1
end
if(not exists(select * from sys.databases WHERE name =@strDBName1)
or not exists(select * from sys.databases WHERE name =@strDBName2)
)
begin
print 'Syntax : exc sp_utl_FindMisMatchedObjects ''DBName'',''DBName2'''
set @iRetVal=1
end
if(@strDBName1=@strDBName2)
begin
print 'Syntax : exc sp_utl_FindMisMatchedObjects ''DBName'',''DBName2'''
set @iRetVal=1
end
if(@iRetval=0)
begin
declare @tblDbObjects
table
(
DBName varchar(20),
ObjectType VARCHAR(20),
ObjectName VARCHAR(500),
CreatedOn DATETIME,
ModifiedOn DATETIME,
Script VARCHAR(MAX)
)
declare @SqlProc1 varchar(MAX)
declare @SqlView1 varchar(MAX)
declare @SqlProc2 varchar(MAX)
declare @SqlView2 varchar(MAX)
select @SqlProc1 = null
,@SqlView1 = null
,@SqlProc2 = null
,@SqlView2 = null
set @SqlProc1=' SELECT DbName =''' +ltrim(rtrim(@strDbName1))+''''
+',ObjectType =''Stored Procedure'''
+',ObjectName =P.name'
+',CreatedOn =P.create_date'
+',ModifiedOn =P.modify_date'
+',Script =ltrim(rtrim(M.definition))'
+ ' FROM '+ltrim(rtrim(@strDbName1))+'.sys.sql_modules M'
+' INNER JOIN '+ltrim(rtrim(@strDbName1))+'.sys.procedures P'
+' ON M.object_id = P.object_id;'
set @SqlProc2=' SELECT DbName =''' +ltrim(rtrim(@strDbName2))+''''
+',ObjectType =''Stored Procedure'''
+',ObjectName =P.name'
+',CreatedOn =P.create_date'
+',ModifiedOn =P.modify_date'
+',Script =ltrim(rtrim(M.definition))'
+ ' FROM '+ltrim(rtrim(@strDbName2))+'.sys.sql_modules M'
+' INNER JOIN '+ltrim(rtrim(@strDbName2))+'.sys.procedures P'
+' ON M.object_id = P.object_id;'
set @SqlView1=' SELECT DbName =''' +ltrim(rtrim(@strDbName1))+''''
+',ObjectType =''View'''
+',ObjectName =v.name'
+',CreatedOn =v.create_date'
+',ModifiedOn =v.modify_date'
+',Script =ltrim(rtrim(M.definition))'
+ ' FROM '+ltrim(rtrim(@strDbName1))+'.sys.sql_modules M'
+' INNER JOIN '+ltrim(rtrim(@strDbName1))+'.sys.views V'
+' ON M.object_id = v.object_id;'
set @SqlView2=+' SELECT DbName =''' +ltrim(rtrim(@strDbName2))+''''
+',ObjectType =''View'''
+',ObjectName =v.name'
+',CreatedOn =v.create_date'
+',ModifiedOn =v.modify_date'
+',Script =ltrim(rtrim(M.definition))'
+ ' FROM '+ltrim(rtrim(@strDbName2))+'.sys.sql_modules M'
+' INNER JOIN '+ltrim(rtrim(@strDbName2))+'.sys.views V'
+' ON M.object_id = v.object_id;'
insert into @tblDbObjects exec (@SqlProc1)
insert into @tblDbObjects exec (@SqlProc2)
insert into @tblDbObjects exec (@SqlView1)
insert into @tblDbObjects exec (@SqlView2)
--select * from @tblDbObjects
select 'Objects Missing from ' + @strDbName2
, DBName1 =db1.DBName
, ObjectType =db1.ObjectType
, CreatedOn1 =db1.CreatedOn
, ModifiedOn1 =db1.ModifiedOn
, DBName2 =db2.DBName
, CreatedOn2 =db2.CreatedOn
, ModifiedOn2 =db2.ModifiedOn
, Script1 =db1.Script
, Script2 =db2.Script
from @tblDbObjects db1
left join @tblDbObjects db2
on db1.DBname<>db2.DbName
and db1.ObjectType=db2.ObjectType
and db1.ObjectName=db2.ObjectName
where db1.DbName=@strDbName1
and db2.ObjectName is null
union all
select 'Objects Missing from ' + @strDbName1
, DBName1 =db1.DBName
, ObjectType =db1.ObjectType
, CreatedOn1 =db1.CreatedOn
, ModifiedOn1 =db1.ModifiedOn
, DBName2 =db2.DBName
, CreatedOn2 =db2.CreatedOn
, ModifiedOn2 =db2.ModifiedOn
, Script1 =db1.Script
, Script2 =db2.Script
from @tblDbObjects db1
left join @tblDbObjects db2
on db1.DBname<>db2.DbName
and db1.ObjectType=db2.ObjectType
and db1.ObjectName=db2.ObjectName
where db1.DbName=@strDbName2
and db2.ObjectName is null
union all
select 'Mismatched Objects between ' + @strDbName1 + ' and ' + @strDbName2
, DBName1 =db1.DBName
, ObjectType =db1.ObjectType
, CreatedOn1 =db1.CreatedOn
, ModifiedOn1 =db1.ModifiedOn
, DBName2 =db2.DBName
, CreatedOn2 =db2.CreatedOn
, ModifiedOn2 =db2.ModifiedOn
, Script1 =db1.Script
, Script2 =db2.Script
from @tblDbObjects db1
join @tblDbObjects db2
on db1.DBname<>db2.DbName
and db1.ObjectType=db2.ObjectType
and db1.ObjectName=db2.ObjectName
where db1.DbName=@strDbName1
and ltrim(rtrim(replace(replace(db1.Script,char(10),' '),char(13),' ')))<>ltrim(rtrim(replace(replace(db2.Script,char(10),' '),char(13),' ')))
end
return @iRetVal
END
go 
