Add standard text to all stored procedures

Question

Add standard text to all stored procedures

Fishbarnriots

SSCrazy

Points: 2568
More actions
January 29, 2010 at 6:54 am

#73983

Hi,
Been struggling with this one for a day or two. As part of our migration and upgrade project (sql 2000 to sql 2008) the development team are going to review all of the user stored procedures to ensure compliance to SQL 2008 standards and to generally tidy things up.
To prevent duplication of effort and to provide some level of accountability it has been decided to add the following block of text to all stored procedures
/'****************************/
'**SQL2008 COMPLIANT = NO **' + CHAR(13) +
'**COMPLETED BY = **'+ CHAR(13) +
'**COMPLETED ON = **'+ CHAR(13) +
'****************************/'
I am trying to achieve this automatically, rather than having the developers pasting the text into each procedure. To this end I have considered adapting SP_Helptext to script the procedures as "Alter" rather than "Create" and inserting the standard text block after "Create Procedure [XXXXX]" and before any parameters being passed in. The trouble is the procedure name from sysobjects is not always reflected in the text in Syscomments (Proc name ABC, Syscomments reads Create Procedure XYZ. My adapted procedure is therefore not 100% efficient.
Does anyone have any suggestions, or indeed a script available that will allow me to get this sorted?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 1

I think something like a SQL Refactor from Red Gate would do this.

Are you not using Source Control? I would make sure you're doing that, and then I would look at scripting out all stored procedures with the Generate Script. I bet a Search and replace might work, or some macro that would allow you to inser this code in there. Perhaps just before the ALTER?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

Try the below mentioned code on test server.

DECLARE @tbl TABLE

(

RowText varchar(max)

)

INSERT INTO @tbl

exec sp_helptext 'dbo.test1'

DECLARE @sp-2 varchar(max)

SELECT @sp-2 = ISNULL(@sp, '') + RowText

FROM @tbl

SET @sp-2 = REPLACE(@sp, 'AS', '/****************************

SQL2008 COMPLIANT = NO + CHAR(13) +

COMPLETED BY = XY+ CHAR(13) +

COMPLETED ON = DATE + CHAR(13) +

***************************/

AS')

SET @sp-2 = REPLACE(@sp, 'Create','Alter')

print (@sp)

exec (@sp)

MJ

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

MANU-J. (1/29/2010)
Try the below mentioned code on test server.
DECLARE @tbl TABLE
(
RowText varchar(max)
)
INSERT INTO @tbl
exec sp_helptext 'dbo.test1'
DECLARE @sp-2 varchar(max)
SELECT @sp-2 = ISNULL(@sp, '') + RowText
FROM @tbl
SET @sp-2 = REPLACE(@sp, 'AS', '/****************************
SQL2008 COMPLIANT = NO + CHAR(13) +
COMPLETED BY = XY+ CHAR(13) +
COMPLETED ON = DATE + CHAR(13) +
***************************/
AS')
SET @sp-2 = REPLACE(@sp, 'Create','Alter')
print (@sp)
exec (@sp)
MJ

Oh... be careful... there's a whole bunch of places where "AS" can be used in a stored procedure including column aliases, table aliases, embedded text, partial words, etc, etc.

--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)

Fishbarnriots SSCrazy Points: 2568 More actions · Answer 4

Many thanks for the suggestions, still working on a solution, will let you know how it goes.

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 5

Is it really worth finding a solution for this? Seems like 4 lines of code that can be pasted in the stored proc once it has been reviewed. Sounds easy enough.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 6

It is easy to paste in, and honestly I'm not sure that I'd do it for this reason. However I do think that this is an interesting idea, and it might be useful for other standardized things. Perhaps setting specific options for consistentcy, or even for benchmarking.

Let us know if it works.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

Fishbarnriots

An area of SSMS you might consider exploring to determine if you can use a template to do what you want to do.... now I am not sure their is a solution within SSMS, but in years gone by (SQL 2000), you could do something close to what you want using a template. So give this a try, and if it works please please post to a forum so others can benefit.

Main menu Click Help

In the drop down menu displayed click on Tutorial

On the page shown select Lesson 4: Working with Templates, Solutions, Script Projects, and Source Control

On the next page work your way through

Creating Scripts Using Templates

Creating Custom Templates

Saving Scripts as Projects and Solutions

Managing Solutions with Source Control

Summary

Hope this helps ...

Thanks to Imu92 if clicking on Tools on the main tool bar does not give the option of seeing existing templates then use a CTRL+ALT T on my SSMS it appears in a separate frame to the right of the query window.

Thanks Lutz

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 8

On my system the 2k5 templates are located at

"C:\Programs\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql\Stored Procedure\"

It's easy to modify those templates.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 9

Ok, here's the summary of a short mail conversation I had in between:

How to open Template Explorer in SSMS:

a) CTRL+ALT+T

b) Menu-> View-> upper section 4th item

Issues I had when trying to change the template:

I opened a template from the template explorer, changed it and saved it as per tutorial.

But it didn't work. The template remained unchanged.

My (dirty) workaround:

Open the template, change it, copy to clipboard, close the template without changes, locate and open the physical file, replace the file with the changed template from clipbard.

I never figured why the tutorial approach didn't work....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 10

Try attached script and comment out last line(EXECUTE sp_executesql @sp3--+'GO') till you are satisfied with output.

MJ

Fishbarnriots SSCrazy Points: 2568 More actions · Answer 11

Manju

Thanks for the code snippet, but as Jeff Moden stated "there's a whole bunch of places where "AS" can be used in a stored procedure ", for example in a parameter (@PASsword), so as I have already discovered using charindex or patindex to find the insertion point is unreliable.

I think there are really only 2 options, get the developers to paste the standard text into the procedure, or look into adding the text at the end of the stored procedure script rather than trying to squeeze it into the header, which is pretty much an impossible task given that there is never going to be any guaranteed method for locating the insertion point.

Thanks to everyone for the suggestions, but as has been stated previously for the sake of a few lines the easy and reliable option is to paste it in by eye.