Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing Text in Multiple Stored Procedures From a Stored Procedure


Changing Text in Multiple Stored Procedures From a Stored Procedure

Author
Message
bdavey
bdavey
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 286
Comments posted to this topic are about the item Changing Text in Multiple Stored Procedures From a Stored Procedure
andy.carter 89422
andy.carter 89422
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 186
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
Joffrey
Joffrey
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 111
Thanks for sharing :-)
lancebeacroft
lancebeacroft
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 6
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!
deroby
deroby
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 289
@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.




Chris Howarth-536003
Chris Howarth-536003
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1159
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
Nick W*
Nick W*
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 180
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.
steven.malone
steven.malone
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 227
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"
lancebeacroft
lancebeacroft
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 6
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
deroby
deroby
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 289
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 :-D (or in fact it was closer to Crying at the time =)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search