0

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?

enter image description here

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
3

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, depentent_name order by depth, row) as rn_type
 FROM recurse r
 )
 SELECT *
FROM RecurseRowNumberPartitionedByDepententType
WHERE rn_type = 1
ORDER BY depth
    ,THE_ORDER
  • ops, I have posted my answer without seen yours, surely I will have a look and let you know. – Marcello Miorelli Oct 28 at 18:28
  • @MarcelloMiorelli - I'm not sure if my solution will work or not as I don't have access to your full range of data. Also, my example uses a different object_id than yours. – Scott Hodgin Oct 28 at 18:30
  • I think that if you had access to my data you would get it straight. The idea and the way of thinking deserves +1, thank you. the result I get is here – Marcello Miorelli Oct 28 at 18:39
  • hmm - what happens if you change my partition by to be ROW_NUMBER() over (partition by depentent_type, depentent_name order by depth, row) as rn_type – Scott Hodgin Oct 28 at 18:47
  • I knew this would be tricky - so I have now updated the result I get, using the link above. Basically it is duplicating some functions – Marcello Miorelli Oct 29 at 11:03
0

I hope to find a better solution, however, just to get this done in a satisfactory way

I create a temp table:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

IF OBJECT_ID('TEMPDB.dbo.#Radhe') IS NOT NULL 
    DROP TABLE #Radhe

CREATE TABLE #Radhe ( 

[proc_id]         INT                                  NOT NULL,
[proc_name]       NVARCHAR(257)                        NOT NULL,
[proc_type]       NVARCHAR(60)                         NOT NULL,
[Parameters]      NVARCHAR(1008)                       NOT NULL,
[depentent_id]    INT                                  NOT NULL,
[depentent_Name]  NVARCHAR(257)                        NOT NULL,
[depentent_type]  NVARCHAR(60)                         NOT NULL,
[depth]           INT                                  NOT NULL,
[row]             BIGINT                               NOT NULL,
THE_ORDER         NVARCHAR(60)                         NOT NULL
)

instead of showing the results, I save them to the temp table:

 INSERT INTO #RADHE
([proc_id],
[proc_name],
[proc_type],
[Parameters],
[depentent_id],
[depentent_Name],
[depentent_type],
[depth],
[row],
THE_ORDER ) 
 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(50))  END + ')'
                              WHEN 'DECIMAL'    THEN '(' + CAST (p.precision AS VARCHAR(50)) + ',' + CAST (p.scale AS VARCHAR(50)) + ')' 
                              WHEN 'NUMERIC'    THEN '(' + CAST (p.precision AS VARCHAR(50)) + ',' + CAST (p.scale AS VARCHAR(50)) + ')' 
                              WHEN 'NCHAR'      THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE  CAST (p.max_length/2 AS VARCHAR(50)) END + ')'
                              WHEN 'NVARCHAR'   THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE  CAST (p.max_length/2 AS VARCHAR(50)) END + ')'
                              WHEN 'VARCHAR'    THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE  CAST (p.max_length AS VARCHAR(50))   END + ')'
                              WHEN 'VARBINARY'  THEN '(' + CASE WHEN p.max_length = -1 THEN 'MAX' ELSE  CAST (p.max_length AS VARCHAR(50))   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
 FROM recurse r

then I use my PICKER to get rid of the excesses:

 ;WITH KALKI_AVATAR AS (
 SELECT 
R.[proc_id],
R.[proc_name],
R.[proc_type],
R.[Parameters],
R.[depentent_id],
R.[depentent_Name],
R.[depentent_type],
R.[depth],
R.[row],
R.THE_ORDER,
Picker= ROW_NUMBER() OVER (PARTITION BY R.[depentent_id]  ORDER BY R.[depth] )
FROM #Radhe R
) 
DELETE FROM KALKI_AVATAR
WHERE PICKER > 1

and ready for the select:

 SELECT 
R.[proc_id],
R.[proc_name],
R.[proc_type],
R.[Parameters],
R.[depentent_id],
R.[depentent_Name],
R.[depentent_type],
R.[depth],
R.[row],
R.THE_ORDER
FROM #Radhe R
ORDER BY r.depth, r.THE_ORDER

enter image description here

not very elegant, but does the job

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.