I often work with third party data professionals and they ask me to replicate whatever table(s) such and such stored procedure or function or view touches within one or more databases.
so I have been developing this CTE, shown below, that does a good work, BUT on the result set it shows the table multiple times as the table might be used in one of the functions dependent on the main one, and had probably already been listed.
I would like those tables to be shown only one, and I am struggling to find an elegant solution to get that done.
for example:
on the picture below the table dbo.Application is listed 5 times, each time because it is used within one of the dependant functions.
But I only need to replicate the table once, so I would like it to be shown only once, how can I achieve that?
here is my current code
please replace 946935291 by the object_id of your own stored procedure for testing.
--==================================================================
--==================================================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;WITH stored_procedures AS (
SELECT
o.object_id as proc_id
,oo.object_id as depentent_id
,SCHEMA_NAME(O.schema_id) + '.' + o.name AS proc_name
,o.type_desc AS proc_type
,depentent_Name =SCHEMA_NAME(OO.schema_id) + '.' + oo.name
,[depentent_type]=oo.type_desc
,ROW_NUMBER() OVER(PARTITION BY o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sys.sql_dependencies d
INNER JOIN sys.objects o ON o.object_id=d.object_id
INNER JOIN sys.objects oo ON oo.object_id=d.referenced_major_id
--WHERE 1 = CASE WHEN @SPID = 0 THEN 1
-- ELSE CASE WHEN @spid = o.object_id
-- THEN 1
--ELSE 0
-- END
-- END
--==================================================================================
-- THE OBJECT TYPES
--AF = Aggregate function (CLR)
--C = CHECK constraint
--D = DEFAULT (constraint or stand-alone)
--F = FOREIGN KEY constraint
--FN = SQL scalar function
--FS = Assembly (CLR) scalar-function
--FT = Assembly (CLR) table-valued function
--IF = SQL inline table-valued function
--IT = Internal table
--P = SQL Stored Procedure
--PC = Assembly (CLR) stored-procedure
--PG = Plan guide
--PK = PRIMARY KEY constraint
--R = Rule (old-style, stand-alone)
--RF = Replication-filter-procedure
--S = System base table
--SN = Synonym
--SO = Sequence object
--==================================================================================
)
--select *
--from stored_procedures sps
--where sps.proc_id = 946935291
--and ROW = 1
, recurse
as (
SELECT depth = 1
,sps.*
,THE_ORDER= CASE WHEN sps.depentent_type='USER_TABLE' THEN '' ELSE sps.depentent_type END
FROM stored_procedures sps
WHERE sps.proc_id = 946935291 -- <-- use this to filter results.
AND sps.row = 1
UNION ALL
SELECT depth = R.depth + 1
,sps.*
,THE_ORDER= CASE WHEN sps.depentent_type='USER_TABLE' THEN '' ELSE sps.depentent_type END
FROM stored_procedures sps
INNER JOIN recurse R
ON SPS.proc_id = R.depentent_id
AND sps.row = 1
)
SELECT r.proc_id
,r.proc_name
,r.proc_type
,[Parameters] = STUFF( ( SELECT ','+
CASE WHEN p.parameter_id = 0 THEN 'RETURNS' ELSE p.name
END -- this is the return value
+
' '
+
UPPER(TYPE_NAME(p.user_type_id))
+
CASE UPPER(TYPE_NAME(p.user_type_id))
WHEN 'CHAR' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length AS VARCHAR) END + ')'
WHEN 'DECIMAL' THEN '(' + CAST (p.precision AS VARCHAR) + ',' + CAST (p.scale AS VARCHAR) + ')'
WHEN 'NUMERIC' THEN '(' + CAST (p.precision AS VARCHAR) + ',' + CAST (p.scale AS VARCHAR) + ')'
WHEN 'NCHAR' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length/2 AS VARCHAR) END + ')'
WHEN 'NVARCHAR' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length/2 AS VARCHAR) END + ')'
WHEN 'VARCHAR' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length AS VARCHAR) END + ')'
WHEN 'VARBINARY' THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE CAST (p.max_length AS VARCHAR) END + ')'
WHEN 'XML' THEN ''
ELSE ''
END
+
CASE WHEN is_output = 1 THEN ' OUTPUT' ELSE '' END
FROM sys.parameters p
WHERE r.proc_id = p.object_id
ORDER BY p.parameter_id FOR XML PATH('')),1 ,1, '')
,r.depentent_id
,R.depentent_Name
,R.depentent_type
,R.depth
,R.row
FROM recurse r
ORDER BY r.depth, r.THE_ORDER
