Select distinct column values from multiple databases

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.

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;

StackExchange Database Administrators Q#292512, answer score: 6