Compare SPs across multiple databases

  • 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

  • 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!

  • Thanks -

    Tom

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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/

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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/

  • 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/

  • 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

  • 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/

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply