New answers tagged

0

This is the code I ended up using: IF OBJECT_ID(N'tempdb..#TempTableName') IS NOT NULL BEGIN DROP TABLE #TempTableName END GO CREATE TABLE #TempTableName (C1 nvarchar(2)) EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'',''master'') BEGIN INSERT INTO #TempTableName SELECT DISTINCT [SOMEFIELD] FROM [?].[dbo].[MYTABLE] ...


6

There were some issues with an earlier version of another answer, so I'm going to put this here, as a slightly different version of the same thing: String aggregation via variable should be avoided, you should use STRING_AGG or FOR XML instead You must use QUOTENAME to quote database names, in case there are characters that need quoting. For example, ...


3

What you're looking for is the UNION operator, though I don't think you can use this with the sp_MSforeachdb procedure. The UNION operator automatically removes duplicates for you. Easiest solution might be to pre-create a #TempTable and leverage that in your call to sp_MSforeachdb like so: CREATE TABLE #TempTableName (ColumName DataType); EXEC ...


Top 50 recent answers are included