March 12, 2012 at 8:46 am
Hi,
I am in a situation where I need to roll out some SPs across a few hundred databases. Redgate's SP compare is great for compare one database schema to another but does anyone how ot a tool/script to compare database schema to multiple databases?
Thanks
Tom
March 12, 2012 at 10:09 am
Hey Buddy,
I have been struggling with same problem since last week. Can you please let me know if you find a work around.
Cheers!
March 12, 2012 at 10:10 am
Thanks -
Tom
March 12, 2012 at 10:17 am
i do this in a programming language so i can use regular expressions;
There are two issues to look at...are two procs EXACTLY the same , character for character, or are the procs functionally the same the same if you ignore whitespace and comments.
EXACTLY the same is sometimes a pain and misleading when there is an extra space or line in the defintion. Even how the proc's script was executed matters, because it might end lines with either vbCrLf or just vbLf.
in VB, I get the definition from sys.sql_modules , strip out comments with a regular expression, then strip out all whitespace (space,tab,Cr,Lf)
if the two are equal after that, they are functionaliy identical, no need for me to do anything...if they are not equal, the definition from the "source" database needs to be created/altered on the target.
I've got a cute little "Schema Checker" program i wrote that does that for all objects, to help me with identifying versioning/development changes between databases.
Lowell
March 12, 2012 at 10:26 am
Conceptually this is challenging. Do you specify the target and compare the others against that? If you are rolling out sprocs to a hundred db's what do you need to compare? Seems that no matter how you slice this it would be manual and super tedious.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2012 at 10:35 am
Essentially all the databases are made from a clone so most of them might be exactly same. But I am loooking for incase for any database if there was some change made to a specific database stored procedure for some desired function.
I would like to know to know such sProc before I roll out a change across all databases.
Cheers
March 12, 2012 at 10:43 am
pallvi.pathania (3/12/2012)
Essentially all the databases are made from a clone so most of them might be exactly same. But I am loooking for incase for any database if there was some change made to a specific database stored procedure for some desired function.I would like to know to know such sProc before I roll out a change across all databases.
Cheers
OK, here's a question from the other side...does it matter if they are differnet or not? why not drop and recreate every proc/view/function from the Perfect/Source database into the target databases, regardless of whether they are the same or not?
if you have to compare to find developer changes, that's the issue i was tackling...but if you know they need to be the same, you could just shortcut the test and just make sure by rebuilding them all no matter what,.
Lowell
March 12, 2012 at 10:44 am
could you just determine the checksum of the text column from sys.syscomments. Then in your rollout script you just include that in your check to determine if the current database needs this update?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2012 at 10:47 am
Lowell (3/12/2012)
i do this in a programming language so i can use regular expressions;There are two issues to look at...are two procs EXACTLY the same , character for character, or are the procs functionally the same the same if you ignore whitespace and comments.
EXACTLY the same is sometimes a pain and misleading when there is an extra space or line in the defintion. Even how the proc's script was executed matters, because it might end lines with either vbCrLf or just vbLf.
in VB, I get the definition from sys.sql_modules , strip out comments with a regular expression, then strip out all whitespace (space,tab,Cr,Lf)
if the two are equal after that, they are functionaliy identical, no need for me to do anything...if they are not equal, the definition from the "source" database needs to be created/altered on the target.
I've got a cute little "Schema Checker" program i wrote that does that for all objects, to help me with identifying versioning/development changes between databases.
That sounds like a very handy utility.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2012 at 10:54 am
HI Lowell,
I am looking for the changes, I would not want to overwrite the SPs if there were some additional functionalities in few databases.
Regards
PAllvi
March 12, 2012 at 10:56 am
pallvi.pathania (3/12/2012)
HI Lowell,I am looking for the changes, I would not want to overwrite the SPs if there were some additional functionalities in few databases.
Regards
PAllvi
That is what I was guessing which is why I suggested using the checksum. The caveat however is what Lowell already pointed out. If there is ANY difference the checksum will be different.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2012 at 11:14 am
to do it all in sql, you'd need a CLR for stripping comments out; then it could all be done via TSQL.
this would be my prototype to start with:
SELECT * FROM
(
SELECT
SCHEMA_NAME(objz.schema_id) AS SchemaName,
objz.name ,
src.definition
--select *
from PERFECT1100.sys.sql_modules src
inner join PERFECT1100.sys.objects objz
ON src.object_id = objz.object_id
) TheSource
LEFT OUTER JOIN
(
SELECT
SCHEMA_NAME(objz.schema_id) AS SchemaName,
objz.name ,
src.definition
--select *
from PERFECT1012.sys.sql_modules src
inner join PERFECT1012.sys.objects objz
ON src.object_id = objz.object_id
) TheTarget
ON TheSource.SchemaName = TheTarget.SchemaName
AND TheSource.name = TheTarget.name
WHERE REPLACE(REPLACE(REPLACE(REPLACE(TheSource.definition,CHAR(13),''),CHAR(10),''),CHAR(9),''),CHAR(32),'')
<> REPLACE(REPLACE(REPLACE(REPLACE(TheTarget.definition,CHAR(13),''),CHAR(10),''),CHAR(9),''),CHAR(32),'')
/*
--If you had a CLR to rip out the comments:
WHERE REPLACE(REPLACE(REPLACE(REPLACE(dbo.CLR_StripComments(TheSource.definition),CHAR(13),''),CHAR(10),''),CHAR(9),''),CHAR(32),'')
<>REPLACE(REPLACE(REPLACE(REPLACE(dbo.CLR_StripComments(TheTarget.definition),CHAR(13),''),CHAR(10),''),CHAR(9),''),CHAR(32),'')
*/
Lowell
March 12, 2012 at 11:16 am
i also have this snippet for stripping comments via TSQL; if you could get that to be an inline function, you'll have it all;
the onyl way i could think of doing it so far was with a cursor and a temp table to strip teh comments.
declare @definition varchar(max),
@objectname varchar(255),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @objectname = 'sp_getDDL'
select @definition = definition + 'GO' + @vbcrlf from sys.sql_modules where [object_id] = object_id(@objectname)
--'objective: strip out comments.
--first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.
--second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.
--===== Replace all '/*' and '*/' pairs with nothing
WHILE CHARINDEX('/*',@definition) > 0
SELECT @definition = STUFF(@definition,
CHARINDEX('/*',@definition),
CHARINDEX('*/',@definition) - CHARINDEX('/*',@definition) + 2, --2 is the length of the search term
'')
--===== Replace all single line comments
WHILE CHARINDEX('--',@definition) > 0
AND CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) > CHARINDEX('--',@definition)
SELECT @definition = STUFF(@definition,
CHARINDEX('--',@definition),
CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) - CHARINDEX('--',@definition) + 2,
'')
print @definition --you can now search this without false positives from comments.
Lowell
March 12, 2012 at 12:32 pm
I saw where you didn't want to overwrite if they had tweaked them.
I have to say that his is a bad plan and bad code management. If you are releasing sproc named X you should have a single "flavor", If someone wants to tweak it, they need to give THEIR version a new name OR they cannot complain when it gets overwritten.
I have been in this situation before and it was a nightmare. When we pulled back and took the position that the base sprocs will be controlled from a central point, it became very easy. We compiled them and if someone had made changes that got lost then they had been warned. This only happened a few times before they got the point and stopped doing it. And our management became much easier.
No major vendor allows you to tweak their sprocs, on updates they can overwrite whatever is their. Their code is god..
CEWII
March 13, 2012 at 8:01 am
Sean Lange (3/12/2012)
Lowell (3/12/2012)
...I've got a cute little "Schema Checker" program i wrote that does that for all objects, to help me with identifying versioning/development changes between databases.
That sounds like a very handy utility.
Sean if you care to test drive it, I can provide you with a link in return for a bit of criticism.
Right now, it's just a personal tool i built based on my own needs for now, but I am kicking the idea around of making it public.
Getting another user's point of view would point out any developer tunnel vision i might have.
Lowell
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy