February 3, 2012 at 11:17 am
Hello Everyone,
I am trying to compare Stored procedures between two databases. I have the following scripts written and another from google. The query works fine, But it is also taking the blank spaces into account and showing that the queries are not matching. I do not know how to eliminate the blank space in comparsion of queries. Can someone modify these queries or give me some queries or suggestions of how to compare stored procedures only text.
-----------------
select P.name as DB1, P.type,M.definition,s.name as DB2, r.definition
from DB1.sys.procedures P left outer join DB2.sys.procedures S
on p.name= s.name left outer join DB1.sys.sql_modules M
on m.object_id = p.object_id left outer join DB2.sys.sql_modules R
on r.definition = m.definition
----------------------
IF OBJECT_ID('CompareObjectCode','P') IS NOT NULL
DROP PROC CompareObjectCode
GO
CREATE PROC CompareObjectCode
@SourceDBName SYSNAME
,@DestDBName SYSNAME
,@ObjectName SYSNAME = '%'
AS
DECLARE @sql VARCHAR(MAX)
SELECT @sql =
'
SELECT ''Object Name'' = ISNULL(SRC.name,Dest.name)
,''Object Type'' = ISNULL(SRC.type_desc,Dest.type_desc)
,''Status'' = CASE
WHEN SRC.Definition is null THEN ''Missing in Source''
WHEN Dest.Definition is null THEN ''Missing in Destination''
WHEN SRC.Definition <> Dest.Definition THEN ''Definition Mismatch''
ELSE ''Match''
END
,''Source Created Date'' = SRC.create_date
,''Source Modify Date'' = SRC.modify_date
,''Destination Created Date'' = Dest.create_date
,''Destination Modify Date'' = Dest.modify_date
FROM
(
SELECT so.name,so.create_date,so.modify_date,sm.definition ,so.type_desc
FROM ' + @SourceDBName + '.SYS.objects so
JOIN ' + @SourceDBName + '.SYS.sql_modules sm
ON so.object_id = sm.object_id
WHERE so.name like ''' + @ObjectName + '''
) Src
FULL OUTER JOIN
(
SELECT so.name,so.create_date,so.modify_date,sm.definition ,so.type_desc
FROM ' + @DestDBName + '.SYS.objects so
JOIN ' + @DestDBName + '.SYS.sql_modules sm
ON so.object_id = sm.object_id
WHERE so.name like ''' + @ObjectName + '''
) Dest
ON SRC.name = dEST.name
'
EXEC (@Sql)
GO
-- To execute the procedure enter the Source DB and Destination DB name.
/* Parameters
@SourceDBName = Source Database Name
@DestDBName = Destination Database Name with which the code has to be compared
@ObjectName = For comparing the required object names only. This parameter accepts wildcard LIKE also.
Default '%' = All Objects
*/
EXEC CompareObjectCode @SourceDBName='DB1',@DestDBName='DB2',@objectname= 'up_%'
February 3, 2012 at 11:30 am
i do something similar for comparing schemas and proc/view/function definitions, but i do it in an application, instead of as a procedure;
my logic: i want to compare functional definitions; i prefer to ignore white space differences, as well as comments contained in the procedure. to do that, i need regular expressions to strip out both kinds of comments, and then simply remove all white space.
the end result is I compare "CREATEPROCASPR_whatever..."
if they are both the same without white space or comments, they must be functionality the same.
to do it the same way I do in TSQL, i think you need a regex CLR, or a multi step process to strip the comments, and then a couple of replaces for space/tab/Cr and LF chars.
does that help any?
Lowell
February 5, 2012 at 2:47 am
This is a simple but incorrect solution:
WHEN REPLACE(REPLACE(REPLACE(SRC.Definition,' ',''),CHAR(13),''),CHAR(10),'')
<> REPLACE(REPLACE(REPLACE(Dest.Definition,' ',''),CHAR(13),''),CHAR(10),'') THEN 'Definition Mismatch'
It will remove spaces and new line characters - you might also need to extend it to include other whitespace characters, but this is a starting point.
The solution is incorrect because whitespace characters won't always be insignificant in your stored procedures. Writing a function to parse your TSQL text and strip out insignificant whitespace isn't easy to do in T-SQL. This sort of thing would be easier to do properly in .NET.
My advice would be to invest in a tool like Red Gate SQL Compare. It makes the task of compare and synchronization simple - It is a commercial tool, but well worth the money if you need to compare on a regular basis.
If you are comparing individual SPs between databases, you might also find my TSQL DDL Code History tool useful. It's an auditing tool for DDL events, but it also includes integration with Diff Merge which makes the task of comparing individual SPs a lot easier.
http://www.wisesoft.co.uk/software/tsql_ddl_code_history_tool/default.aspx
It can also help compare entire databases using only freeware tools, but again I would suggest investing in SQL Compare if you need to do this on a regular basis.
Hope this helps,
David
DBA Dash - Free, open source monitoring for SQL Server
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply