Simple SQL to find tables that are used in a stored procedure. #mssql #sql #sqlserver #storedprocedure

StoredProcedure_Table_Relation.sql

DECLARE @temptableforSP TABLE  (spName varchar(100), tableName nvarchar(100))
DECLARE @SP_Name as NVARCHAR(100);
DECLARE @SP_Cursor as CURSOR;
 
SET @SP_Cursor = CURSOR FOR
	SELECT [name] FROM sys.objects WHERE name LIKE 'sp%' AND type='P' -- Gets SPs for specific names
 
OPEN @SP_Cursor;
FETCH NEXT FROM @SP_Cursor INTO @SP_Name;
WHILE @@FETCH_STATUS = 0
BEGIN
 --print(@SP_Name)
 INSERT INTO @temptableforSP
	 SELECT 
		OBJECT_NAME(referencing_id) AS referencing_entity_name,    
		referenced_entity_name     
	FROM sys.sql_expression_dependencies AS sed  
	INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
	WHERE referencing_id = OBJECT_ID(@SP_Name); 

 FETCH NEXT FROM @SP_Cursor INTO @SP_Name;
END
 
CLOSE @SP_Cursor;
DEALLOCATE @SP_Cursor;       

-------Display temp. table for SP and Table relation
SELECT * FROM @temptableforSP
------------------