--==================================================================
--==================================================================
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 = 20351287
--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 = 858642302 -- <-- 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
)
,RecurseRowNumberPartitionedByDepententType as
(
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
,r.THE_ORDER
,ROW_NUMBER() over (partition by depentent_type, depentent_name order by depth, row) as rn_type
FROM recurse r
)
SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
ORDER BY depth
,THE_ORDER
--==================================================================
--==================================================================
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 = 20351287
--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 = 858642302 -- <-- 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
)
,RecurseRowNumberPartitionedByDepententType as
(
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
,r.THE_ORDER
,ROW_NUMBER() over (partition by depentent_type, depentent_name order by depth, row) as rn_type
FROM recurse r
)
SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
ORDER BY depth
,THE_ORDER
--==================================================================
--==================================================================
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 = 20351287
--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 = 858642302 -- <-- 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
)
,RecurseRowNumberPartitionedByDepententType as
(
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
,r.THE_ORDER
,ROW_NUMBER() over (partition by depentent_type, depentent_name order by depth, row) as rn_type
FROM recurse r
)
SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE rn_type = 1
ORDER BY depth
,THE_ORDER
--==================================================================
--==================================================================
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 = 20351287
--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 = 858642302 -- <-- 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
)
,RecurseRowNumberPartitionedByDepententType as
(
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
,r.THE_ORDER
,ROW_NUMBER() over (partition by depentent_type, depentent_name order by depth, row) as rn_type
FROM recurse r
)
SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
ORDER BY depth
,THE_ORDER
--==================================================================
--==================================================================
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 = 20351287
--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 = 858642302 -- <-- 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
)
,RecurseRowNumberPartitionedByDepententType as
(
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
,r.THE_ORDER
,ROW_NUMBER() over (partition by depentent_type order by depth, row) as rn_type
FROM recurse r
)
SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
ORDER BY depth
,THE_ORDER
--==================================================================
--==================================================================
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 = 20351287
--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 = 858642302 -- <-- 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
)
,RecurseRowNumberPartitionedByDepententType as
(
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
,r.THE_ORDER
,ROW_NUMBER() over (partition by depentent_type, depentent_name order by depth, row) as rn_type
FROM recurse r
)
SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
ORDER BY depth
,THE_ORDER
Try this and let me know.
I added one more cte selecting from recurse to add a ROW_NUMBER() over (partition by depentent_type order by depth, row) as rn_type and added at the bottom WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
--==================================================================
--==================================================================
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 = 20351287
--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 = 858642302 -- <-- 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
)
,RecurseRowNumberPartitionedByDepententType as
(
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
,r.THE_ORDER
,ROW_NUMBER() over (partition by depentent_type order by depth, row) as rn_type
FROM recurse r
)
SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
ORDER BY depth
,THE_ORDER
lang-sql