FIND THE SP THAT HAVE TABLE1 - 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 Administration (2000)

FIND THE SP THAT HAVE TABLE1

Author

Topic

bassem_farouk
Starting Member

18 Posts

Posted - 2010-10-18 : 20:15:02

HI
I WANT TO SEARCH ALL THE STORED PROCEDURES FOR A WORD?
EX: I WANT TO FIND ALL STORED PROCEDURES THAT CONTAIN THE TABLE TABLEXYZ
HOW CAN I DO THAT?

THANKS

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-18 : 20:17:48

You can search syscomments.

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

Subscribe to my blog

bassem_farouk
Starting Member

18 Posts

Posted - 2010-10-18 : 21:37:06

Hi

it does not work

select * from syscomments
where text like '%table1%'

Thanks

bassem_farouk
Starting Member

18 Posts

Posted - 2010-10-18 : 21:40:21

sorry,
it works but i can;t read the ctext and text columns

Thanks

bassem_farouk
Starting Member

18 Posts

Posted - 2010-10-18 : 22:01:50

thanks i did it
SELECT
sysobjects.name AS [Trigger Name],
SUBSTRING(syscomments.text, 0, 2060) AS [Trigger Definition],
OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
syscomments.encrypted AS [IsEncrpted]

FROM
sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE
text like '%TABLE1%'

Thanks

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-10-19 : 15:27:32

Go to http://www.red-gate.com/products/SQL_Search/index.htm

Download the tool (it is free) - and use it to search. Works very well.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources