How to identify all stored procedures referring a particular table

Question

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

User · Answer

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

User · Answer

You have basically 2 options   ----Option 1  SELECT DISTINCT so name FROM syscomments sc INNER JOIN sysobjects so ON sc id so id WHERE sc TEXT LIKE   tablename     ----Option 2  SELECT DISTINCT o name  o xtype FROM syscomments c INNER JOIN sysobjects o ON c id o id WHERE c TEXT LIKE   tablename     These 2 queries will get you all the stored procedures that are referring the table you desire  This query relies on 2 sys tables which are sysobjects and syscomments  The sysobjects is where all of your DB object names are stored this includes the stored procedures    The syscomments contains the text for all of your procedures   If you query   SELECT   FROM syscomments   You ll have a table containing the id which is the mapping to the sysobjects table with the text contained in the stored procedures as the last column

User · Answer

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

User · Answer

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

User · Answer

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    lt TABLE NAME gt

User · Answer

SELECT Name FROM sys procedures WHERE OBJECT DEFINITION OBJECT ID  LIKE   TableNameOrWhatever     BTW -- here is a handy resource for this type of question  Querying the SQL Server System Catalog FAQ

User · Answer

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

User · Answer

SELECT     o name FROM     sys sql modules sm INNER JOIN sys objects o ON     o object id   sm object id WHERE     sm definition LIKE    lt table name gt      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

User · Answer

Try This      SELECT DISTINCT so name     FROM syscomments sc     INNER JOIN sysobjects so ON sc id so id     WHERE sc TEXT LIKE   your table name

User · Answer

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

User · Answer

This useful query also works if you are using Azure SQL   Synapse Anlaytics SELECT DISTINCT o name AS Object Name o type desc FROM sys sql modules m INNER JOIN sys objects o ON m object id o object id WHERE m definition Like   Table Name

User · Answer

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

[sql] How to identify all stored procedures referring a particular table

Examples related to sql

Examples related to tsql

Examples related to sql-server-2005