Introduction

This is an interesting one – it took me a bit of time to write, as I did it to prove to myself I could as much as anything! I’ve always had a number of developers working in my team, and whilst we always put version control procedures in to place, having multiple sites has always caused an issue with them forgetting to deploy a change across all databases. This code basically compares the content of the procedures between a source and destination database and if they do not match, displays the results. It uses a cursor which was the main reason I wrote it – I’m use to working in a set based method, rather than a procedural method so wanted to expand my knowledge of the sql language / functions but I have subsequently found this code very useful.

The Code

declare @spname as nvarchar(50)
declare @srcdb as nvarchar(20) = ‘<db1>’
declare @destdb as nvarchar(20) = ‘<db2>’

— This gets all of the Stored Procedures in the source System

DECLARE db_cursor CURSOR FOR

select specific_name
from live_app.information_schema.routines
where routine_type = ‘PROCEDURE’
and (specific_name like ‘pbti%’
or specific_name like ‘EXT%’)

— create a table to store results
IF OBJECT_ID(‘tempdb..##PBTI_SpContent2’) IS NOT NULL
DROP TABLE ##PBTI_SpContent2
create table ##PBTI_SpContent2 (
spname nvarchar(50)
,dcontent  nvarchar(max)
, scontent  nvarchar(max)
)
— This bit gets the stored procedures content of each table

open db_cursor
fetch next from db_cursor into @spname

WHILE @@FETCH_STATUS = 0
Begin

declare @strdest as nvarchar(max) = ‘insert into ##PBTI_SpContent2  (spname, dcontent, scontent)

SELECT
”’+@spname +”’
,d.definition
,s.definition
from ‘+@destdb+’.sys.sql_modules as d
left join ‘+@srcdb+’.sys.sql_modules as s on 1 = 1
where
d.object_id = OBJECT_ID(N”’+@destdb + ‘.dbo.’ + @spname+”’)
and s.object_id = OBJECT_ID(N”’+@srcdb + ‘.dbo.’ + @spname+”’);
‘
exec sp_executesql @strdest

fetch next from db_cursor into @spname
end
close db_cursor
deallocate db_cursor
select
*
from ##PBTI_SpContent2
where replace(replace(replace(replace(replace(replace(rtrim(ltrim(dcontent)),’ ‘,”),CHAR(13),”),CHAR(10),”),'[dbo].’,”),'[‘,”),’]’,”)
<>
replace(replace(replace(replace(replace(replace(rtrim(ltrim(scontent)),’ ‘,”),CHAR(13),”),CHAR(10),”),'[dbo].’,”),'[‘,”),’]’,”)