How can I get the list of tables in the stored procedure?

Question

There are lot of tables and sp in the db. I find the tables name which are used in the specific sp (stored procedure).

sp_depends %sp_name% not give the desire result. I am also used INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.ROUTINES tables.

But the result is not full fill my requirment.

Answer 1

Try more elegant way (but, it's solution works only in MS SQL 2008 or higher) -

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

Answer 2

The two highest voted answers use a lot of deprecated tables that should be avoided.
Here's a much cleaner way to do it.

Get all the tables on which a stored procedure depends:

SELECT DISTINCT p.name AS proc_name, t.name AS table_name FROM sys.sql_dependencies d  INNER JOIN sys.procedures p ON p.object_id = d.object_id INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id ORDER BY proc_name, table_name 

Works with MS SQL SERVER 2005+

List of Changes:

• sysdepends should be replaced with sys.sql_dependencies
  • The new table uses object_id instead of id
  • The new table uses referenced_major_id instead of depid
• Using sysobjects should be replaced with more focused system catalog views
  • As marc_s pointed out, instead use sys.tables and sys.procedures
  • Note: This prevents having to check where o.xtype = 'p' (etc.)

• Also, there is really no need for a CTE which uses ROW_NUMBER() just in order to make sure we only have one of each record set returned. That's what DISTINCT is there for!

  • In fact, SQL is smart enough to use DISTINCT behind the scenes.

  • I submit into evidence: Exhibit A. The following queries have the same Execution Plan!

    -- Complex WITH MyPeople AS (   SELECT id, name,   ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id, name) AS row   FROM People) SELECT id, name FROM MyPeople WHERE row = 1  -- Better SELECT DISTINCT id, name FROM People 

Answer 3

;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, 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')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

Answer 4

Here is the sql code for this

To get list of tables used in a stored procedure

;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, 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')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

.

Reverse - To find Stored Procedure Related to Table in Database – Search in All Stored Procedure

There two ways to this

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

PS: sp_help and sp_depends does not always return accurate results.

Reference:

1. Sql Server Central - Get list of tables used in a stored procedure
2. SqlAuthority - Find Stored Procedure Related to Table in Database – Search in All Stored Procedure