I have a table that identifies the table name, Key field and a inventory ID value which I can use to identify what tables I would need to use in a union query in order to pull all records by asset type (table). I there a way I can use this table to loop through each record in the table to build my union query instead of manually creating. This way, if we add another asset type, I would not have to modify the union query, it would dynamically include it once it was added.
sorta think you're looking for something like this:
DECLARE @SQL varchar(max)
SELECT @SQL =COALESCE(@SQL,'')+'SELECT '''+servername+''' as servername, '''+dbname+''' as dbname,1 as [yourField(s)]
FROM ['+servername+'].'+dbname+'.dbo.YourTableNameHere aliasHere
WHERE 1=1 --replace with condition here
UNION
'
FROM dbo.serverReference
WHERE--change this WHERE clause as necessary
dbname LIKE'%myDatabase%'
--chop off the last "UNION" from the string
SET @SQL =SUBSTRING(@SQL,1,LEN(@SQL)-7)+'ORDER BY dbname'
PRINT @SQL
EXEC(@SQL)
17 People are following this question.
