How can I find all occurrences of particular text

问题:

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.

回答1:

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

sysobjects

syscomments

回答2:

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%'

回答3:

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

回答4:

Try this one -

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

回答5:

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.