How to get the all table(s) name which are used in particular stored procedure?

Question

I have a requirement to get all the database tables name which are used in specific stored procedure?

As an example, I have one stored procedure as given below.

CREATE PROCEDURE [dbo].[my_sp_Name]
    @ID INT = NULL
AS
BEGIN

    SELECT ID, NAME, PRICE

    FROM tbl1
    INNER JOIN tbl2 ON tbl1.ProductId = tbl2.ProductId
    LEFT JOIN tbl3 ON tbl2.ProductSalesDate = tbl3.ProductSalesDate
    LEFT JOIN tbl4 ON tbl1.ProductCode = tbl4.ItemCode
END

Expected output:

Used_Table_Name

1. tbl1
2. tbl2
3. tbl3
4. tbl4

Can any one suggest a way?

Answer 1

Below query will help you to get used database tables in stored procedure.

;WITH stored_procedures AS (  
    SELECT oo.name AS table_name,  
    ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row  
    FROM sysdepends d  
    INNER JOIN sysobjects o ON o.id=d.id  
    INNER JOIN sysobjects oo ON oo.id=d.depid  
    WHERE o.xtype = 'P' AND o.name = 'my_sp_Name'
)  
SELECT Table_name AS 'Used_Table_Name' FROM stored_procedures  
WHERE row = 1

Answer 2

Use below script to get table names in your store procedure :

  SELECT DISTINCT [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
        , o.type_desc
  FROM sys.dm_sql_referenced_entities ('[dbo].[Your_procedurename]',  
  'OBJECT')d
  JOIN sys.objects o ON d.referenced_id = o.[object_id]
  WHERE o.[type] IN ('U', 'V')