Select distinct column values from multiple databases

Question

I have 100 databases from customers who all have the same schema but different content.

Now I wanted to some analysis and start out with running a distinct of one column over all databases. My instance contains these and more.

I think it is close to

EXEC sp_MSforeachdb 'Use ? select distinct [ColumName] from [TableName]'

However, that does not combine them; surrounding this with a select also does not work.

Answer 1

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, consider what happens if there is a database called My]Database, or My]..SomeTable;DROP DATABASE OtherDatabase; --.

Solution:

DECLARE @DynamicSQL nvarchar(max) =
(
 SELECT STRING_AGG(CAST(N'
SELECT [ColumnName] 
FROM ' + QUOTENAME(D.[name]) + '.SchemaName.TableName
'
 AS nvarchar(max)), N'
UNION
')
 FROM sys.databases
);
PRINT @sql; -- for testing
EXEC sp_executesql @sql;

Answer 2

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 sp_MSforeachdb 'Use ? INSERT INTO #TempTableName SELECT [ColumName] FROM [TableName]';
SELECT DISTINCT ColumName
FROM #TempTableName

Answer 3

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]
END 
'
SELECT DISTINCT C1, count(c1) FROM #TempTableName 
group by c1
order by count(c1)