Query Stored Procedure code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Stored Procedure code

Author

Topic

ds12will
Starting Member

21 Posts

Posted - 2009-11-20 : 15:22:30

Is there anyway to query the coding for stored procedures for example

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'

the problem is the definition gets cut off after certain amount of characters

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 15:24:29

either script out the sproc from EM or SSMS

Or do sp_helptext <sprocname>

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ds12will
Starting Member

21 Posts

Posted - 2009-11-20 : 15:28:52

quote:
Originally posted by X002548

either script out the sproc from EM or SSMS

Or do sp_helptext <sprocname>

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sorry I should have been clearer. I'm converting all our DB "stuff" to UTC time and since its a pretty large DB, I'm doing a search through all the stored procedures

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%getdate%' AND ROUTINE_TYPE='PROCEDURE'

what I want to know is for each procedure, how many times is getdate found in the respective code??

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 15:36:27

Are all of your sprocs save to a folder on a drive?

(If not they should be...how else do you do change control?)

Read this, but then also look at the comments...we just develped a newer version that I'm gonna have to post sooner or later...let me know how this works for you...or the comments suggestions at the bottom of the blog

http://weblogs.sqlteam.com/brettk/archive/2004/02/05/841.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources