Create a View from Multiple Databases

Question

Database X - tableCust - Column-CustNumber
Database Y - tableCust - Column-Custnumber
Datbase Z - tableCust - Column-Custnumber

I'm trying to create a view in database H from those three databases like below.

Select Custnumber,Orginal_DB_Name() as DatabaseName
from X.dbo.tablecust
Union 
Select Custnumber,Orginal_DB_Name() as DatabaseName
from Y.dbo.tablecust
;
;
;
etc

But the database name is showing me Master instead of their DB_Name.

It needs to show output like below:

Database_Name CustNumber
X 221
X 1223
Y 122
Y 233
"
"

• Ali Razeghi - AWS: What is the code for Original_DB_Name()? You might be using a function that displays the database the query was executed from rather than the specific section it is working on.
• billinkc: Assuming you're generating code, you could have SELECT S.name, DB_NAME(DB_ID('msdb')) AS DatabaseName FROM msdb.sys.schemas AS S and tokenize the database name but if that's the case, I'd probably just be lazy and implement as SELECT S.name, 'tempdb' AS DatabaseName FROM tempdb.sys.schemas AS S
• Aaron Bertrand: ORIGINAL_DB_NAME() is a function that returns the original database context for the login, even if they have since changed database context. If you're going to use a function, you should look up what it does in the documentation. :-) And if you're going to hard-code from X.dbo.tablecust you may as well also hard-code 'X' as DatabaseName... you can automate this, easy or hard depending on what version of SQL Server you're using.
• Deana: I'm confused...
• Ali Razeghi - AWS: Hehe I was trying to have him look up the documentation to see that it pulled up the original context, but Aaron hit it on the head.

Answer 1

Specify each database name explicitly as a string:

SELECT
 Custnumber,
 'X' AS DatabaseName
FROM
 X.dbo.tablecust
UNION
SELECT
 Custnumber,
 'Y' AS DatabaseName
FROM
 Y.dbo.tablecust
UNION
...

There is no other way. After all, you have to explicitly specify the database name in the table references too (X.dbo.tablecust, Y.dbo.tablecust etc.).

• db2: Including a constant column like this in a view of unions is also helpful for the optimizer. If you were to do SELECT * FROM MyView WHERE DatabaseName = 'X', you'd notice SQL Server is smart enough to completely ignore all the other parts of the union, and only run the portions with that value in the select list.

Answer 2

In SQL Server 2016 and above, you could specify the table name, column name(s), and a list of databases as a simple string and build your view dynamically:

USE H;
GO
DROP VIEW IF EXISTS dbo.MyView;
GO
DECLARE 
 @table nvarchar(511) = N'dbo.tablecust', 
 @column sysname = N'Custnumber', 
 @dbs nvarchar(max) = N'X,Y,Z',
 @sql nvarchar(max) = N'';
;WITH dbs(db) AS
(
 SELECT value FROM STRING_SPLIT(@dbs, N',')
)
SELECT @sql += N'
 UNION ALL ' + -- usually people mean UNION ALL, not UNION
 N'
 SELECT ''' + db + N''' AS Database_Name,' + @column + N'
 FROM ' + QUOTENAME(db) + N'.' + @table
FROM dbs;
SET @sql = N'CREATE VIEW dbo.MyView
AS' + STUFF(@sql, 1, 13, '') + N';';
PRINT @sql;
EXEC sys.sp_executesql @sql;

In this case this generates the following view code:

CREATE VIEW dbo.MyView
AS 
 SELECT 'X' AS Database_Name,Custnumber
 FROM [X].dbo.tablecust
 UNION ALL 
 SELECT 'Y' AS Database_Name,Custnumber
 FROM [Y].dbo.tablecust
 UNION ALL 
 SELECT 'Z' AS Database_Name,Custnumber
 FROM [Z].dbo.tablecust;

And with the sample data from the question (and two rows I made for the database Z), a select from that view yields these results:

enter image description here

There are solutions for older versions too, but they are a little more complex and only worth exploring when we know they're necessary.