List All the SP - 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

SQL Server 2000 Forums

Transact-SQL (2000)

List All the SP

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-06-13 : 00:54:50

Hi All,
I want to retrive all the coding of the stored procedure in my db. I know for viewing the coding i have to use sp_helptext. How to loop through the sp and list all the list ?

Thanks in Advance
Dana

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 01:30:58

SELECT [text]
FROM syscomments
WHERE [text] IS NOT NULL

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-13 : 01:31:42

I prefer to use INFORMATION_SCHEMA.ROUTINES rather than syscomments.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 01:38:56

[code]SELECT ROUTINE_NAME,
ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_BODY = 'SQL'
AND ROUTINE_TYPE = 'PROCEDURE'[/code]

E 12°55'05.25"
N 56°04'39.16"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-06-13 : 01:44:48

Very Nice :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-13 : 09:49:19