Search all stored procedures - 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

SQL Server Development (2000)

Search all stored procedures

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2005-08-18 : 10:39:10

I need to search through about 100 stored procs. Can I search using sql instead of scripting all 100 out to a text file?

thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-18 : 10:49:54

What information are you searching for?
See if this helps you

Select routine_name, routine_definition from information_Schema.routines
where routine_definition like '%somestring%'

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-18 : 20:49:21

There's also:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32319

INFORMATION_SCHEMA.ROUTINES only returns the first 4000 characters of the procedure text, so in case you have procedures longer than that, sp_findtext will work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 00:34:00

Thats cool rob
I did not know routines return first 4000 characters only

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-19 : 07:45:39

Neither did I until I checked the definition for it. Pretty annoying really, but it's documented that way in BOL.

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-19 : 09:55:38

Look here....you still gotta script them, but so what

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