Changing Text in Multiple Stored Procedures From a Stored Procedure

  • Comments posted to this topic are about the item Changing Text in Multiple Stored Procedures From a Stored Procedure

  • Thanks for the post - tried this out and works great. I always change any CURSORS which I find in code to a WHILE Loop though. There's not many times when you cannot do this - you just need a unique Id you can leverage. In this example you can use the DBId from sysdatabases. You then process each database in turn. They key is selecting a TOP 1 in dbid order so that they are processed in order without missing one out. See the code below to see how the generation step is replaced with a WHILE loop.

    DECLARE @DB varchar(200), @SQL varchar(2000), @dbid INT

    SET @dbid = 0

    WHILE EXISTS (SELECT TOP 1 * FROM master.dbo.sysdatabases WHERE name NOT IN('master','msdb','model','tempdb')

    AND dbid > @dbid)

    BEGIN

    SELECT TOP 1 @dbid = dbid, @DB=name FROM master.dbo.sysdatabases WHERE name NOT IN('master','msdb','model','tempdb')

    AND dbid > @dbid

    ORDER BY dbid

    SET @SQL = 'WITH id_cte (id)

    AS

    (Select id From ' + QUOTENAME(@DB) + '.sys.sysobjects

    Where Not name Like ''sp_MSsync%''

    )

    SELECT ' + QUOTENAME(@DB,'''') + ', id, colid, ctext

    FROM ' + QUOTENAME(@DB) + '.sys.syscomments

    WHERE id IN(Select Distinct id

    From ' + QUOTENAME(@DB) + '.sys.syscomments

    Where [text] Like ''%admission%'')

    And id In(Select id From id_cte)'

    INSERT INTO Identity_SProcs(dbName,id,colid,spText)

    EXEC(@SQL)

    END

  • Thanks for sharing 🙂

  • Sorry, but I am not happy with this solution at all.

    My view is that any changes to procedures must be auditable. therefore, first off I would have identified the procedures that had @@identity.

    Select object_name(id) from syscomments where text like '%@@identity%'

    But my aim is to create an auditable rollout script so with the same query I would then amend it to:

    Select 'Sp_Helptext ' + object_name(id) as result from syscomments where text like '%@@identity%'

    You will also need to create the drop commands for later

    Select 'Drop Procedure ' + object_name(id) as result from syscomments where text like '%@@identity%'

    then put the results of the drop query at the top of your script for later along with the use database and any other rollout function you do. etc.

    When you run the Sp_helptext query you should output the results of that SQL to a text file. Effectivley you are now creating a basic rollout script.

    If you have chosen to leave in the headers for the query then with the script, you need to only search and replace Text with Go, and remove altogether the string '------------------' or whatever is being used to underscore the heading.

    Once you have this script ready to execute, and of course you would never do anything like this against a live server at this stage, or without a backup, you can then perform your search and replace for @@Identity, replacing it with @@scope_Identity on the files contents.

    You have now created the script for testing which should execute as necessary, but if not fix the errors that have occurred.

    But you now have script that can be placed intou your version software database along with notes as to what is being changed, why it is being changed and that is vital for developers coming after you, You then release your changes as you would any other database update.

    My point here is that if you must start with the database as your repository for the code, and I can understand why that is given that there are thousands of procedures for you to edit, there is no reason why you should not get back into the normal development flow at the earliest opportunity.

    Ok, Discuss!

  • @Andy.carter 89422: although I'm not a fan of cursors at all and will always try to avoid them in favour of a SET-based solution, 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. Hugo Kornelis did some research on this subject here.

    In my real-world tests I've never had a situation where the WHILE solution was faster than the CURSOR solution. I know cursors are considered 'evil', but in fact it's not the cursors that are evil, it's their usage (line by line processing) that is the root of the problem. "Hiding" the word 'cursor' using a WHILE loop does not fix this but rather makes things more complicated and prone to bugs over time.

    BTW, totally off-topic but I also think it's a good idea to have the INSERT () INSIDE the @SQL variable instead of before the EXEC() statement. This will allow the optimizer to see both the INSERT and the SELECT as one big operation, while otherwise it would see them as sequential operations; doing whatever is in the EXEC first and taking those results to the INSERT in a separate operation. As an added bonus it avoids the dreaded "Unable to do nested INSERT EXEC" (or something along those lines) error when you (accidentally) try to nest this kind of statements, e.g. when calling a stored procedure that makes use of this and whose output you're trying to catch.

    Sadly the 'Show Execution Plan' does not show the INSERT () EXEC() approach, for reasons I don't know, the entire statement seems to be omitted from the query plan completely ???? (SQL2008 here). You can show it by analysing the results from STATISTICS CPU & TIME, but it takes a bit of puzzling to see the operations are split, and (as expected) sum up to taking more cpu-time, more elapsed-time & more IO

    Sample code (EDIT : BTW: yes, I know, the code is not 100% correct in relation to the article, it only finds the "lines" of code that have an m in it, not the entire code of the object. (in fact I started off from sql_modules, realised people might want to run this on older versions too and then forgot to adapt, sorry):

    SET STATISTICS IO ON

    SET STATISTICS TIME ON

    DECLARE @SQL nvarchar(max)

    DECLARE @DB sysname

    SELECT @DB = 'msdb'

    SELECT @SQL =

    'INSERT INTO Identity_SProcs(dbName,id,colid,spText)

    SELECT ' + QUOTENAME(@DB,'''') + ', c.id, c.colid, c.ctext

    FROM ' + QUOTENAME(@DB) + '..syscomments c

    JOIN ' + QUOTENAME(@DB) + '..sysobjects o

    ON o.id = c.id

    AND o.[name] NOT LIKE ''sp_MSsync%''

    AND o.xtype = ''P''

    WHERE c.[text] LIKE ''%m%'''

    EXEC (@SQL)

    TRUNCATE TABLE Identity_SProcs

    SELECT @SQL = '

    SELECT ' + QUOTENAME(@DB,'''') + ', c.id, c.colid, c.ctext

    FROM ' + QUOTENAME(@DB) + '..syscomments c

    JOIN ' + QUOTENAME(@DB) + '..sysobjects o

    ON o.id = c.id

    AND o.xtype = ''P''

    AND o.[name] NOT LIKE ''sp_MSsync%''

    WHERE c.[text] LIKE ''%m%'''

    INSERT INTO Identity_SProcs(dbName,id,colid,spText)

    EXEC (@SQL)

    which then results in

    Table 'Identity_SProcs'. Scan count 0, logical reads 925, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 1, logical reads 3268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysobjvalues'. Scan count 1, logical reads 293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'syscolpars'. Scan count 1, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysschobjs'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 109 ms, elapsed time = 137 ms.

    vs

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysobjvalues'. Scan count 1, logical reads 293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'syscolpars'. Scan count 1, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'sysschobjs'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 47 ms, elapsed time = 55 ms.

    Table 'Identity_SProcs'. Scan count 0, logical reads 919, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 1, logical reads 5146, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 78 ms, elapsed time = 98 ms.

  • lancebeacroft (3/28/2011)


    Sorry, but I am not happy with this solution at all.

    My view is that any changes to procedures must be auditable. therefore, first off I would have identified the procedures that had @@identity.

    I'm of a similar mindset.

    Visual Studio's 'Replace in Files' command could be used to replace @@IDENTITY with SCOPE_IDENTITY() across all of the relevant script files, then use Red-Gate SQL Compare to compare the 'before' and 'after' script files to generate synchronisation scripts.

    Much less risky IMHO, plus you will also capture occurrences of @@IDENTITY in any objects encrypted in the database.

    Chris

  • We have to do something very similar, and I like some aspects of the OP's approach, but I'd like to share some experience, having done it daily for the last five years or so:-

    - The word "CREATE" can occur in a lot of places, so you have to make sure that you replace "CREATE PROC" with "ALTER PROC".

    - Because there can be an arbitrary number of spaces between CREATE and PROC, you have to use another fine tip from this very site to quickly reduce CREATE(\s+?)PROC to CREATE\sPROC (to use a regexpism).

    - If you have RENAMED the stored procedure, then you have to be careful to re-execute the stored procedure using the NEW name, not the old. SQL Server does some funky re-writing in this case.

    - Unless you are careful to script them out, the "SET" lines at the top and tail of the Procedure will be lost, so doing this can chance the ANSI_NULLS and other settings that were present when the SP was first created.

    - General Note: There's an obvious problem with replacing common text. You could easily shred your Db by putting in the replacing of "CREATE" with "ALTER" across the board.... @CreatedDate would become @AlterdDate for example... So, try it out on a test Db first!

    Hope this helps,

    N.

  • With a little more research you would have found sys.sql_modules which has the advantage of the definition field being nvarchar(max) so sprocs > 8k do not need to be reassembled.

    As posted your solution will not work in SQL 2k anyway since you use varbinary(max).

    Also I do not understand why you are using varbinary instead of nvarchar. Then the built in replace function would work as expected.

    All that being said: overall kudos for showing how a smart DBA can simplify complex tasks using SQL.

    "Real DBAs don't write code, they write code that writes code for them"

  • Thanks nick,

    My problem with the solution here is that the DBA is changing the code without the advantage of Alter or drop/create procedure. Would SQL server then regenerate the query plan for these amended procedures.

    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

  • As for doing things this way versus via scripts & source-control : I think the idea of making sure you get all the @@IDENTITY usages this way isn't all too bad. It has a couple of pitfalls though. For starters you'll miss out on the encrypted objects regardless of the implementation. Secondly, via syscomments you risk losing those that have @@ID on the first line and ENTITY on the second line (if you catch my drift), and then there's the risk of case-sensitivity (or not).

    Personally I would rather go over all the source-scripts, update as needed and roll out again. Once finished with that, run the script of the poster again to identify any stray @@IDENTITY usages and either remove them from the database since they are obviously not under source-control and thus likely "illegal" or "obsolete". Or add & fix them in the source-control system and redeploy them to the databases again.

    I guess it's a matter of what you're used to ... I've also done some 'the db is the source system' projects in the past, but I've been burnt enough to realise the value of a source-control system 😀 (or in fact it was closer to :crying: at the time =)

  • i think there's a great sql search tool that does that and even better

  • lancebeacroft (3/28/2011)


    My problem with the solution here is that the DBA is changing the code without the advantage of Alter or drop/create procedure. Would SQL server then regenerate the query plan for these amended procedures.

    Article


    replaces CREATE PROCEDURE with ALTER PROCEDURE. It then prints the corrected stored procedure to the message window.

    So the process just generates text for the scripts to alter the procedures. Which means it is using an alter on the proc so the plan will be regenerated.

    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.

    I do kind of agree with this. Far better to have all the scripts in source control and do the fine/replace in that then compare the versions of the files to make sure nothing got mucked with. However, Brian was likely in a situation where that wasn't possible and this is a good solution for situations like that.

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

  • I use something similar for searching all stored procedures. I run into a problem where the full procedure doe snot fit in the syscomments field. Will this work on really long procedures?

  • CornholioDBA (3/28/2011)


    I use something similar for searching all stored procedures. I run into a problem where the full procedure doe snot fit in the syscomments field. Will this work on really long procedures?

    You could use sys.sql_objects which has the sql-code in one big varchar(max)...

    Or if you're on an older version of MSSQL you'll need to be creative in walking through the records and sticking the ends & starts of lines together to make sure you catch the situation where the string you're searching for is split across two lines.

    (a bit of a puzzle, might be fun exercise for a sql-challenge =)

    Otherwise the system is quite sound for searching IMHO. Just remember that encrypted objects will not be included in your search.

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

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