SQL - Get Databasename from merged query filter

user_2910863 0
Viewed 44

Good afternoon,

I have a working query, where I loop through all my databases, and filter down to check which report is being used where. This works (see below)

I have this working query:

SET NOCOUNT ON;

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
      ReportPath VARCHAR(500)
      )

declare @SQL nvarchar(max)

set @SQL = STUFF((SELECT '

UNION ALL
' + 'SELECT path FROM ' + quotename(name) + '.dbo.ReportConfig where path like ''%/Standard Reports/Booking/Booked Out by Location%'' and Active = 1'
from sys.Databases 
WHERE name LIKE 'SFB-%' 
FOR XML PATH(''), type).value('.','varchar(max)'),1,15,'')

INSERT #temp
execute(@SQL)

SELECT  ReportPath FROM #temp

And this is giving me the following output:

enter image description here

So I know that out of my 90 databases, the report is being used 6 times, but I don't know where.

So I want to include the database name of where this report is being used. I googled around and tried a bunch of things, but I can't get it to work.

Any ideas?

tsql sql-server
1 Answers

Just a small tweak to what you already have will get you there

SET NOCOUNT ON;

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

--Add a column to your temp table
CREATE TABLE #temp
(
      DatabaseName varchar(100)
      ,ReportPath VARCHAR(500)
      )

declare @SQL nvarchar(max)

--adjust your dynamic query and add the [name] column as shown below
set @SQL = STUFF((SELECT '

UNION ALL
' + 'SELECT ''' + [name] + ''' as DatabaseName,path FROM ' + quotename(name) + '.dbo.ReportConfig where path like ''%/Standard Reports/Booking/Booked Out by Location%'' and Active = 1'
from sys.Databases 
WHERE name LIKE 'SFB-%' 
FOR XML PATH(''), type).value('.','varchar(max)'),1,15,'')

INSERT #temp
execute(@SQL)

SELECT  DatabaseName, ReportPath FROM #temp
user_103765370
Related