Need a query to update all the stored procedures in a table
-
Hi All,
I need to update all the stored procedures in the specific database with a t-sql statement
I need to insert the "set no count on" before the stored procedure statement and insert "set no count off" at the end of the stored procedure statement.
It is a pain to do it manually as there are 240 stored procedures, how can i go about doing this with a script??
please do help!!!
-
This might well be possible, but I'd rather go through each Store Procedure and ctrl-v the text in than experiment with code that is going to re-write 240 Stored Procedures in a couple of milliseconds.
The thought of what could happen gives me the shivers.
-
Why not script all the stored procs change them from create to alter insert the line you need in the text file and rerun the script so it alters all the stored procs in one go.
-
You can achieve this task by using UltraEdit. What you need is to create a Macro and run it.
My Macro code looks like the following:
InsertMode
ColumnModeOff
HexOff
Loop 50
TrimTrailingSpaces
Find ")^PAS^PSET NOCOUNT ON"
Replace All ") ^PAS SET NOCOUNT ON"
Save
CloseFile
EndLoop
Hope this help! Remember to modify the code before applying on your task requirements for sp update
-
Dangerous proposition to perform find and replace globally
quick example:
on the previous macro the fin was searchin for ") AS"
Problems:
1. if there is not exaclty the same number of spaces that proc is not going to be updated
2. suppose in the code you have inthe proc a subquery like :
(select x, y ,z from table where k =2 ) AS ...
if you replace that can you guess what you just did?
Conclusion:
Either bite the bullet and go one by one or if you have a stored procedure generator update it and regenerate them all
Cheers,
* Noel -
It is just a suggestion and I can understand noeld's concern. In the past, I have completed at least 4 times of this kind of mass update with more than 500 hundreds sps in our SQL Server database such as resized a parameter value, replaced a specific sp exec statement, added "SET COUNT ON" statement, replaced and added "WITH_ENCRYPTION", "SET NOCOUNT ON", and "SET CONCAT_NULL_YIELDS_NULL OFF" commands, etc. May be I should clarify one crucial factor; your sp must be standardized and strictly followed the format. In addition, you have to analyze whether it is applicable in your situation. My advice is to copy few of your sps into your local HD and try it out first before you jump into it. Again, I have done that at least 4 times with different updates and this works for me!!! Here I am just sharing my experience for the matter. Good luck!
-
ArthurC,
Don't get me wrong, I was just making the case that is likely to fail if no generator of some sort was used
I have been involved with clients that use a template for all their stored procedures and they even go as far as to mark certain points in the body of the procedure with comments. For them I am sure your approach will work
* Noel -
Thanks for the help guys
this is what i did i wrote a script that will search for the first occurence of the key word
'AS' and then replace that with 'AS' + Chr(13) + ' set nocount ON'
my intial proiblem where was how to set 'set nocount off' since i could find the end of line since there was no unique character to search for to reach the End of line,
i then read that is no need to set the key word 'set nocount off' as it is local to the executing sp and will reset back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply