Find number of tables used in stored procedure.

While doing analysis or reviewing database objects often we used to look and think for objects which are being used in any specific stored procedure or stored procedures. Following is the small script which is used to find out all the table used in stored procedures.

-- Let's create few tables
CREATE TABLE table1 (id INT)
CREATE TABLE table2 (id INT)
CREATE TABLE table3 (id INT)

-- Create a sample stored procedure to select table
CREATE PROCEDURE sp1
AS
SELECT id
FROM table1

-- Create a sample stored procedure to select table1 and table2
CREATE PROCEDURE sp2
AS
BEGIN
	SELECT table1.id
	FROM table1
	INNER JOIN table2 ON table1.id = table2.id
END

-- Create a sample stored procedure to select table1,table2 and table3
CREATE PROCEDURE sp3
AS
BEGIN
	SELECT table1.id
	FROM table1
	INNER JOIN table2 ON table1.id = table2.id
	INNER JOIN table3 ON table1.id = table3.id
END

— Query to get list of all table used in an SP

SELECT Object_name(referencing_id) ParentObject
	,referenced_entity_name InnerObject
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referenced_entity_name = o.NAME
WHERE o.type = 'U'
	AND OBJECT_NAME(referencing_id) = 'sp1'

The following is output

— Query to get list of all table used in more than 1 SP

SELECT Object_name(referencing_id) ParentObject
	,referenced_entity_name InnerObject
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referenced_entity_name = o.NAME
WHERE o.type = 'U'
	AND OBJECT_NAME(referencing_id) = 'sp1'

The following is output

Assuming we have a list of SPs, and we need to find out all tables used in all SPs of this list. 

Let’s say the following is the said list of Sps

1. sp1
2. sp2
3. sp3

The very first thing we need to do is, Create a temp (#) table and insert all this in a Temp (#)table.

-- Create table
Create table #check(tempid int IDENTITY(1,1),spname char(100))
--Insert records
Insert into #check values ('sp1'),('sp2'),('sp3')
--Verify records
Select * from #check

–Query to get list of all Tables used in all SPs

SELECT Object_name(referencing_id) ParentObject
	,referenced_entity_name TableName
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referenced_entity_name = o.NAME
INNER JOIN #check ON Object_name(referencing_id) = #check.spname
WHERE o.type = 'U'
GROUP BY tempid
	,Object_name(referencing_id)
	,referenced_entity_name
ORDER BY #check.tempid

Following is output

–Query to get count of all Tables used in all SPs

SELECT Object_name(referencing_id) ParentObject ,
       COUNT(referenced_entity_name) TableCount
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referenced_entity_name = o.NAME
INNER JOIN #check ON Object_name(referencing_id) = #check.spname
WHERE o.type = 'U' GROUP BY tempid ,Object_name(referencing_id)
ORDER BY #check.tempid

Following is output

–Query to get the list of all other objects (Tables, Views, Stored Procedure,Functions) used in SP

SELECT Object_name(referencing_id) ParentObject
	,referenced_entity_name InnerObject
	,type_desc InnerObjectType
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o ON d.referenced_entity_name = o.NAME
INNER JOIN #check ON Object_name(referencing_id) = #check.spname
GROUP BY tempid
	,Object_name(referencing_id)
	,referenced_entity_name
	,type_desc
ORDER BY #check.tempid
	,type_desc

Thanks
Manish