3

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.

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

SELECT DISTINCT ColumName
FROM #TempTableName
0
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]
END 
'

SELECT DISTINCT C1, count(c1) FROM #TempTableName 
group by c1
order by count(c1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.