0

I have the start of a query below which gives me dependencies of a particular table:

SELECT DISTINCT OBJECT_NAME(object_id) AS referencing_object_name FROM sys.sql_dependencies WHERE referenced_major_id = OBJECT_ID('TABLE_NAME_HERE')

But is there a way to alter this to show:

1) The above with a column with tablename being populated

2) The above relating to ALL tables within a set database (not just a single table as the original query shows)

3) All results on a single row

Final output looking like image below:

screenshotoffinalquery

. . .

EDIT: I seem to be very close with this, but last column is duplicating a single result

        SELECT DISTINCT b.name,  a.referenced_major_id, b.object_id,

        substring((
        SELECT '   ||   ' +OBJECT_NAME(a.object_id)
        FROM sys.sql_dependencies a JOIN sys.tables b ON a.referenced_major_id = b.object_id
        For XML PATH ('')
        ), 2, 1000) AS [TextLine]

    FROM sys.sql_dependencies a JOIN sys.tables b ON a.referenced_major_id = b.object_id
    ORDER BY b.name ASC
glass_kites
  • 401
  • 4
  • 18
  • Well for #1 - you might take a look at sys.sql_dependencies and see what information is available. #2 - seriously? You would remove the where clause. #3 - you can create comma delimited lists like that using FOR XML. – Sean Lange Oct 13 '16 at 14:24
  • Can't believe I overlooked #2. Cheers! – glass_kites Oct 13 '16 at 15:15
  • You seem to have found all but the hard part now. :) To generate the delimited list you need a little bit more logic. Take a look here to see how this is accomplished. http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server – Sean Lange Oct 13 '16 at 15:23

0 Answers0