Changing Text in Multiple Stored Procedures From a Stored Procedure

  • As more or less already said, however technically a 'smart' solution, the fact that this is the chosen way reveals that sourcecontrol and auditing of changes is hardly done in the company this was applied.

    If you do proper source control with sql scripts in SSMS or Visual Studio projects and using VSS or TFS, there are much better ways to do a find and replace in all scripts and deploy the changed stored procedures in a controlled manner.

  • lancebeacroft (3/28/2011)


    I can't think of a situation where I would use the posters solution. It feels somewhat 'cowboy' and slap dash for my liking.

    L

    (Also @GrassHopper )

    I agree, but consider the case where you're managing database pairs, let's call them "OLTP" and "OLAP", and you have views on the OLAP database:-

    OLAP.vw_referenceData1 -> OLTP.referenceData1

    OLAP.vw_referenceData2 -> OLTP.referenceData2

    .

    .

    Now, if you want to create a copy of these databases for QAing, UAT, Demos, Training and so on, you have to be able to bulk-change all the views, stored procedures and functions to change the main database.

    Yes. This is horrible. No, I would never advise anyone do this, because it's not optimal. Yes, it's a maintenance nightmare. But some people have situations such that this.

    So, what you need in this case is something similar to what the OP suggests, so that you can quickly re-generate all the structure without having to deploy your full DB source, and then 'pour in' all the extra data.

    Let me re-iterate. I *DO NOT* condone this, I just have to do it, cos it's a system I took over from others. So while the OP's solution isn't fantastic, there is a niche out there for mass-changes such as this. My only criticism of it is that it might not go far enough for it to be useful to a lot of people..

    Here's our version (Horrible as it is!). Don't reply with the crapness of the code. I know. But it's used several times a day every day, so I know it works.

    /****** Object: StoredProcedure [dbo].[dba_MassRename] Script Date: 02/10/2009 00:57:04 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_MassRename]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[dba_MassRename]

    GO

    CREATE PROCEDURE dba_MassRename

    @TextFrom varchar(50),

    @TextTo varchar(50)

    AS

    BEGIN

    SET NOCOUNT ON;

    -- loop through three times. First time, Views, second time SPS, third time functions.

    DECLARE @actions TABLE

    (

    sysobjtype1 varchar(10),

    sysobjtype2 varchar(10),

    wordtochange varchar(20)

    )

    INSERT INTO @actions (sysobjtype1,sysobjtype2,wordtochange)

    SELECT 'V','V','VIEW'

    UNION

    SELECT 'P','P','PROC'

    UNION

    SELECT 'TF','FN','FUNC'

    DECLARE @objnames TABLE

    (

    Objname varchar(255)

    )

    DECLARE @t1 varchar(10)

    DECLARE @t2 varchar(10)

    DECLARE @wtc varchar(10)

    DECLARE @objname varchar(255)

    DECLARE @objtext nvarchar(max)

    DECLARE @objtexttmp nvarchar(max)

    DECLARE @CT int

    DECLARE @modct int

    SELECT @CT = count(*) from @actions

    WHILE @CT > 0

    BEGIN

    SELECT TOP(1) @t1 = sysobjtype1, @t2 = sysobjtype2, @wtc = wordtochange

    FROM @actions

    print 'PROCESSING ' + @wtc + 'S

    '

    SET @modct = 0

    DELETE @objnames

    INSERT INTO @objnames (objname)

    SELECT DISTINCT NAME FROM sys.objects where type = @t1 or type = @t2

    DECLARE OBJCURSOR CURSOR FOR

    SELECT objname from @objnames

    OPEN OBJCURSOR

    FETCH NEXT FROM OBJCURSOR INTO @objname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Get the text of the thing we want to work on.

    SELECT @objtext = object_definition(object_id(@objname))

    if @objtext is not null

    BEGIN

    -- change any target DB back, just in case. (prevents the XXXXXUATUAT case

    set @objtext = REPLACE(@objtext,@TextTo,@TextFrom)

    if charindex(@TextFrom,@objtext,1) > 1

    BEGIN

    SET @modct = @modct + 1

    -- GET RID OF SPARE SPACES

    SET @objtexttmp = ''

    WHILE @objtexttmp <> @objtext

    BEGIN

    SET @objtexttmp = @objtext

    SET @objtext = REPLACE(@objtexttmp,'CREATE ','CREATE ')

    END

    -- Change CREATE FUNC to ALTER FUNC

    set @objtext = REPLACE(@objtext,'CREATE '+@wtc,'ALTER '+@wtc)

    -- Change the SRC DB to tgt DB

    set @objtext = REPLACE(@objtext,@TextFrom,@TextTo)

    -- re-execute the item

    BEGIN TRY

    exec sp_executeSQL @objtext

    END TRY

    BEGIN CATCH

    print 'Problem processing ' + @wtc + ' ' + @objname

    END CATCH

    END

    END

    FETCH NEXT FROM OBJCURSOR INTO @objname

    END

    CLOSE OBJCURSOR

    DEALLOCATE OBJCURSOR

    print LTRIM(RTRIM(STR(@modct))) + ' object(s) processed

    '

    SELECT TOP(1)@t1 = sysobjtype1,

    @t2 = sysobjtype2,

    @wtc = wordtochange

    FROM @actions

    DELETE FROM @actions where wordtochange = @wtc

    SELECT @CT = count(*) from @actions

    END

    SELECT name as [Unprocessed Objects] from sysobjects so

    inner join syscomments sc on so.id = sc.id

    where sc.text like '%'+@TextFrom+'%'

    and sc.text not like '%'+@TextTo+'%'

    SELECT name as [Processed Objects] from sysobjects so

    inner join syscomments sc on so.id = sc.id

    where sc.text like '%'+@TextTo+'%'

    and sc.text not like '%'+@TextFrom+'%'

    END

    <warning type="A$$Covering"> If you want to play with this, please do.. If you can improve it, I'd love to make it better.. but if you're a SQL novice, please don't use this blindly. Make sure you know what you're doing, and above-all, test it on a copy first 🙂 </warning>

    N.

  • lancebeacroft (3/28/2011)


    Ahh, now we are in a different scenario. What or who would stop you adopting best practice?

    Time (yes, it's a time savings to do it right but no time right now to set it up.) Money (can't afford a source control system or the systems to put it up on ). Tradition (no one wants to change how it's been done because it works.)

    No, none of these are great arguments but you deal with the situation you're in. Not the ideal one.

  • Interesting article and all. But I hope I'm never in a situation to need this. I've always had employers and clients that have stored proc scripts (plus many other) under source control. There's something to be said for process, even when it slows us down.

  • sada235 (3/28/2011)


    There's something to be said for process, even when it slows us down.

    Amen to that.

  • I would use sys.sql_modules or INFORMATION_SCHEMA.routines. However, more importantly than that, I would convert the @@IDENTITY to the output parameter method suggested by Microsoft since there is a bug related to identity:

    http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

    Other than that, I would always execute code that executes alters on the procedures for change tracking history in the metadata.

    Nice thought provoking article.

  • deroby (3/28/2011)


    I sincerely think changing a cursor into a WHILE loop to be a bad idea. The while-loop approach (sometimes called 'a poor man's cursor') hardly ever brings benefits but potentially can cause the performance to drop noticeable. It all depends a bit on the amount of data to be fetched, as well as the layout and search-ability of said data.

    Ah... someone who takes after my own heart. I've never seen a decent While Loop drop performance compared to a Cursor before. Other than that, I absolutely agree... especially since the "guts" of Cursor usage are (drum roll please)... While Loops. 🙂

    One company I worked for had a consultant come in prior to my being hired. They had some serious performance problems and an average of 640 deadlocks per day with spikes to 4000, to boot. The consultant told them that changing all of their Cursors to Temp Tables and While loops would really go a long way in solving their problems. Of course, after paying that consultant a ridiculous amount of money and spending a quarter million dollars in man power and repairing a bunch of code their misguided efforts screwed up, it made no difference at all... especially when their loops continued to call more than 30 different stored procedures up to 8 levels deep that were all operating on a RBAR basis. Simply changing the Cursors to Forward Only Read Only Static (think system-provided Temp Table) would have been just as effective and a whole lot easier and cheaper to do. It still wouldn't have helped the deadlocks, though... they had a single 3rd-party-provied stored proc which updated a (ugh!) sequence table. Repairing that dropped the deadlocks to almost zero deadlocks that day and every day. The rest came from other, ummm.... less than optimal code.

    Don't get me wrong... I'm still a bit of an anti-Cursor zealot as I am with any form of RBAR or Hidden RBAR... but changing Cursors to Temp Tables and While loops just isn't something I'd spend any time on. And, yes, there are some places where Cursors and While Loops are justified but not in the places a lot of folks think.

    Damn... that turned into a bit of a rant, didn't it? :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @bdavey,

    Although I don't necessarily agree with the methods you used, I have to agree with what has already been said... this is the general direction DBA's should think in when faced with such large problems. It also pulled out some other good suggestions in the discussion. Nicely done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for taking the time to post your article and solution for this topic.

    Though most wouldn't want to use a cursor or RBAR script in the database, how many use sp_msforeachtable or sp_msforeachdb? I would prefer set-based solutions, but this is an administrative type script that won't be run millions of times an hour. Also, it should only be run by admins on an infrequent basis on a dev server to generate the scripts that would be promoted to the QA and then beta and then finally Production servers. When done in such a fashion the RBAR script would never touch the production server.

    Thanks again for the article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • @CornholioDBA

    Sorry for the delay. I've been out of town all last week.

    Yes, this will handle even very large stored procedures. The only thing you need to watch for is that you don't exceed the buffer size of the message window. Off hand I don't know what that is though.

    Thanks

  • I'm not the most experienced with the intricacies of SQL Server, but replacing one bit of text with another bit of text is something I do a LOT of. I don't need a fancy SQL script to do it either...

    1: Right click the database

    2: Click Tasks

    3: Click Generate Scripts

    4: Select the items you would like to modify (All SPROCs?)

    5: Use Find and replace (plain old text editor...) to replace CREATE PROCEDURE with ALTER PROCEDURE

    6: Find and replace the thing you want... IE, @@Identity with Whatever

    No cursors, no whiles, no lingering extra code... straight and simple.

    What's wrong with this approach? 🙂

  • [Responding to: kevonh]

    Nothing wrong with your approach, specifically. But this thread is about an article posted here:

    http://www.sqlservercentral.com/articles/Stored+Procedures/72623/

    And in the first paragraph, the article's author states that s/he is trying to solve a problem across multiple servers and databases. Your approach really only would be good for a single database.

    Regards.

  • Despite what's been said I quite like this.

    It's versatile and can be used to change any element of the script (i.e. table references) and if you have to edit several hundred procedures across numerous db's then it's a massive time saver. Sometimes an approach like this simply is the best way. Source control is great in an ideal world but not so useful when you're being told 'I need this to happen yesterday'.

    And besides... any mistake made can easily be converted back using the same method!

    One small addition - Put something in the naming convention that will stop the 'Get' and 'Print' procedures from appearing as Alter scripts themselves

    By adding:

    AND LEFT(name,3) != 'dba'

    to the first where clause in the 'Get' procedure I was able to prevent this from happening

  • Thanks carmstrong592. That's exactly what I was going for.

Viewing 14 posts - 16 through 28 (of 28 total)

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