CODEWITHSUNDEEP
sqlt-sqlstored-proceduressql-server-2005
DJay
DJay

Reputation: 2567

How to identify all stored procedures referring a particular table

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.

Upvotes: 189

Views: 816345

Answers (15)

Wayne Hamberg
Wayne Hamberg

Reputation: 193

This will tell you every table name and column name that is referenced in every procedure in a database.

This does not include views, functions, etc...

The problem frequently when learning a database is that you aren't interested in just where a table or a column is being used but how is that table and column are being used. I have C# code that provides that information but that's a bit too lengthy to post here.

I am more interested if the table is being inserted into, deleted from or updated or just selected. I want to know if that table/column is joining to another table, used in a WHERE, GROUP BY, ORDER BY or aggregated.

Upvotes: 1

LCJ
LCJ

Reputation: 22662

For Azure SQL, followig can be used to check external table as well.

sys.dm_sql_referenced_entities If a stored procedure is the specified referencing entity, this function returns all user-defined entities that are referenced in the stored procedure such as tables, views, user-defined types (UDTs), or other stored procedures

SELECT B.name + '.'+A.name AS Proc_Name, ROW_NUMBER() OVER(ORDER BY A.name) AS Row_Num
INTO #ProcNames
FROM sys.Procedures A
INNER JOIN sys.schemas B
    ON A.schema_id = B.schema_id
ORDER BY A.name

--DROP TABLE #Result
CREATE TABLE #Result(Proc_Name VARCHAR(255), Table_Name VARCHAR(255), External_Datasource_Name VARCHAR(255))

DECLARE @spName VARCHAR(255)
DECLARE @Iterator INT = 0
DECLARE @ProcCount INT

SELECT @ProcCount = COUNT(*)
FROM #ProcNames

SELECT @ProcCount AS '@ProcCount'

WHILE @Iterator < @ProcCount
BEGIN
    SET @Iterator = @Iterator+1;

    
    SELECT @spName = Proc_Name
    FROM #ProcNames
    WHERE Row_Num = @Iterator

    INSERT INTO #Result
    SELECT DISTINCT @spName AS Proc_Name,
          [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name,  ----Add schema name prefix
          ED.name AS External_Datasource_Name
    FROM sys.dm_sql_referenced_entities (@spName, 'OBJECT') d
    INNER JOIN sys.objects o 
        ON d.referenced_id = o.[object_id]
    INNER JOIN sys.tables T
        ON T.object_id = O.Object_ID
    LEFT OUTER JOIN sys.external_tables E
        ON E.object_id = t.object_id
    LEFT OUTER JOIN sys.external_data_sources ED
        ON ED.data_source_id = E.data_source_id
    WHERE o.[type] IN ('U', 'V')
    --AND ED.name IN ('abc', 'xxx' ) -- DataSource_Name

END

SELECT *
FROM #Result
ORDER BY Proc_Name, External_Datasource_Name, Table_Name

Upvotes: 0

Chains
Chains

Reputation: 13167

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

Upvotes: 388

Ajay
Ajay

Reputation: 105

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Upvotes: -1

KALYAN KONDAPURAM
KALYAN KONDAPURAM

Reputation: 9

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

Upvotes: 0

Alain Ghawi
Alain Ghawi

Reputation: 95

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.

Upvotes: 1

Pooja Chavan
Pooja Chavan

Reputation: 1

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

Upvotes: -1

nzrytmn
nzrytmn

Reputation: 6981

In management studio you can just right click to table and click to 'View Dependencies' enter image description here

than you can see a list of Objects that have dependencies with your table :enter image description here

Upvotes: 8

Siraj Ansari
Siraj Ansari

Reputation: 61

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

Upvotes: 6

Pramod Pawar
Pramod Pawar

Reputation: 411

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

Upvotes: 41

ric
ric

Reputation: 31

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.

Upvotes: 3

Deepak Kothari
Deepak Kothari

Reputation: 1763

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

Upvotes: 10

Hans Kesting
Hans Kesting

Reputation: 39358

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.

Upvotes: 22

Guy Hollington
Guy Hollington

Reputation: 586

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

Upvotes: 39

Tom H
Tom H

Reputation: 47402

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.

Upvotes: 6

Related Questions