CODEWITHSUNDEEP
sqlsql-servert-sql
glass_kites
glass_kites

Reputation: 411

Why is this T-SQL duplicating results

Why is this T-SQL duplicating results?

This query is fairly generic, so should replicate ok in any DB.

I get USPs duplicating in the TextLine column...

SELECT DISTINCT b.name as TableName,  
        STUFF((
             SELECT ', ' + OBJECT_NAME(a.object_id)
             FROM sys.sql_dependencies a 
             WHERE a.referenced_major_id = b.object_id
             ORDER BY 1
             For XML PATH ('')
            ), 1, 1, '') AS [TextLine]
FROM sys.sql_dependencies a JOIN sys.tables b ON a.referenced_major_id = b.object_id
ORDER BY 1 ASC

Upvotes: 2

Views: 101

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your query is overly complicated. You don't need a join in the outer query, and probably not the select distinct there either:

SELECT t.name as TableName,
       Stuff((SELECT DISTINCT ', ' + Object_name(d.object_id) --Here
              FROM   sys.sql_dependencies d
              WHERE  d.referenced_major_id = t.object_id
              ORDER  BY 1
              FOR XML PATH ('')
             ), 1, 1, '') AS [TextLine]
FROM sys.tables t
ORDER BY TableName ASC;

Actually, removing the outer JOIN probably removes the duplicates in the inner query as well:

SELECT t.name as TableName,
       Stuff((SELECT ', ' + Object_name(d.object_id) --Here
              FROM   sys.sql_dependencies d
              WHERE  d.referenced_major_id = t.object_id
              ORDER  BY 1
              FOR XML PATH ('')
             ), 1, 1, '') AS [TextLine]
FROM sys.tables t
ORDER BY TableName ASC;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Add DISTINCT to the sub-query

SELECT DISTINCT b.name                               AS TableName,
                Stuff((SELECT DISTINCT ', ' + Object_name(a.object_id) --Here
                       FROM   sys.sql_dependencies a
                       WHERE  a.referenced_major_id = b.object_id
                       ORDER  BY 1
                       FOR XML PATH ('')), 1, 1, '') AS [TextLine]
FROM   sys.sql_dependencies a
       JOIN sys.tables b
         ON a.referenced_major_id = b.object_id
ORDER  BY TableName ASC

Now for each table only distinct depend objects will be concatenated

Upvotes: 4

Related Questions