How to identify all stored procedures referring a

问题:

I created a table on development environment for testing purpose and there are few sp's which are refreing this table. Now I have have to drop this table as well as identify all sp's which are referring this table. I am facing difficulty to find list of all sp's. Please suggest some query by assuming that the table name is 'x' and database is sql server 2005.

回答1:

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

BTW -- here is a handy resource for this type of question: http://msdn.microsoft.com/en-us/library/ms345522%28v=SQL.90%29.aspx

回答2:

The following works on SQL2008 and above. Provides a list of both stored procedures and functions.

select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
  from sys.objects o inner join sys.sql_expression_dependencies  sd on o.object_id = sd.referenced_id
                inner join sys.objects sp on sd.referencing_id = sp.object_id
                    and sp.type in ('P', 'FN')
  where o.name = 'YourTableName'
  order by sp.Name

回答3:

A non-query way would be to use the Sql Server Management Studio.

Locate the table, right click and choose "View dependencies".

EDIT

But, as the commenters said, it is not very reliable.

回答4:

sometimes above queries will not give correct result, there is built in stored procedure available to get the table dependencies as:

EXEC sp_depends @objname = N'TableName';

回答5:

SELECT
    o.name
FROM
    sys.sql_modules sm
INNER JOIN sys.objects o ON
    o.object_id = sm.object_id
WHERE
    sm.definition LIKE '%<table name>%'

Just keep in mind that this will also turn up SPs where the table name is in the comments or where the table name is a substring of another table name that is being used. For example, if you have tables named "test" and "test_2" and you try to search for SPs with "test" then you'll get results for both.

回答6:

The following query will fetch all Stored Procedure names and the corresponding definition of those SP's

select 
   so.name, 
   text 
from 
   sysobjects so, 
   syscomments sc 
where 
   so.id = sc.id 
   and UPPER(text) like '%<TABLE NAME>%'

回答7:

The query below works only when searching for dependencies on a table and not those on a column:

EXEC sp_depends @objname = N'TableName';

However, the following query is the best option if you want to search for all sorts of dependencies, it does not miss any thing. It actually gives more information than required.

 select distinct
        so.name
        --, text 
  from 
       sysobjects so, 
       syscomments sc 
  where 
     so.id = sc.id 
     and lower(text) like '%organizationtypeid%'
  order by so.name

回答8:

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'table_name' + '%'

GO

This will work if you have to mention the table name.

回答9:

In management studio you can just right click to table and click to 'View Dependencies'

than you can see a list of Objects that have dependencies with your table :