Search store procedures content

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 2005 Forums

Transact-SQL (2005)

Search store procedures content

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-11-09 : 22:09:30

Hi,

Is it possible to search the content in store procedures? For example, I need to scan through and list out all the store procedures where it contains 'LINE'?

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-09 : 23:20:58

[code]
SELECT object_name(id) sp
FROM syscomments
WHERE text like '%line%'[/code]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-10 : 00:59:34

try this too..

select specific_name from INFORMATION_SCHEMA.ROUTINES
where routine_definition like '%line%'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 04:25:45

Beware of the 4000 character limitation in routine_definition and row split in syscomments.

The best approach is

select specific_name from INFORMATION_SCHEMA.ROUTINES
where object_definition(object_id(specific_name)) like '%line%'

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-09 : 07:15:00

Hi

What about this..


SELECT SPNAME = OBJECT_NAME(OBJECT_ID),* FROM SYS.SQL_MODULES
WHERE DEFINITION LIKE '%text%'

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-10 : 01:25:27

quote:
Originally posted by rajdaksha

Hi

What about this..

SELECT SPNAME = OBJECT_NAME(OBJECT_ID),* FROM SYS.SQL_MODULES
WHERE DEFINITION LIKE '%text%'

-------------------------
R...

You can use this too. No truncation

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources