I am working on a script that gives me a list of tables that use a specific stored procedure.
It also works on all tables used by a stored procedure.
on the example below, all stored procedures that use the table 'ProductItemDetailsDenorm'
USE US15WINMPRODUCT GO DECLARE @sp_name nvarchar(128) -- NULL shows all stored procedures that use the table @table ,@table nvarchar(128) SELECT @sp_name = NULL ,@table = 'ProductItemDetailsDenorm' ;WITH stored_procedures AS ( SELECT schema_name(o.schema_id) as proc_schema, o.name AS proc_name, schema_name(oo.schema_id) as table_schema, 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 sys.objects o ON o.object_id=d.id INNER JOIN sys.objects oo ON oo.object_id=d.depid WHERE o.type = 'P' ) SELECT proc_schema, proc_name, table_schema, table_name FROM stored_procedures WHERE [row] = 1 AND ((@sp_name IS NULL) OR (proc_name = @sp_name)) AND ((@table IS NULL) OR (table_name = @table)) ORDER BY proc_name,table_name --sp_help 'sys.objects'
This gives me the list below (partial):
The question, the bit I am struggling with is the following situation:
what if inside the stored procedure SP1 there is a stored procedure SP2 that uses the table @MyTable?
I want that to be listed as well.
I thought the solution could be:
there should be a recursive cte to cater for procedures inside procedures that might use the table @table
but so far I have failed to implement it.
recursive CTEs should have 2 parts: anchor and UNION ALL. how would I apply these two in the current CTE?