How can I find all occurrences of particular text

Question

I need to determine if it is possible to generate a list of stored procedures, where the stored procedure source code contains particular text, such as "table_name".

Thanks in advance.

Answer 1

The stored procedure text (also views, functions and triggers) are captured in sys.sql_modules in SQL 2008.

Try this:

select object_name(object_id)
from sys.sql_modules
where definition like '%table_name%'

and this to display the actual object type next to the name:

select b.name, b.type_desc
from sys.sql_modules a
join sys.objects b      on a.object_id = b.object_id
where a.definition like '%table_name%'

Answer 2

If you are using SSMS here's an even better tool - and it's free! RedGate make a SQL Search utility SQLSearch that is fantastic. It will let you search table names, column name, stored procs, functions just about everything you could look for. And it will do it across multiple databases. I love all of the RedGate tools.

And did I mention it's free? Highly recommended.

Answer 3

Try this one -

SELECT *
FROM sys.dm_sql_referencing_entities ('dbo.table1', 'OBJECT');

Answer 4

Take a look in syscomments for the text and join to sysobjects for the object type and name

sysobjects

syscomments

Answer 5

If You want to listing the Store procedure related particular table than in Sql server right click on particular table and click on view dependencies this will listing all dependent item including store procedure also