0
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
"
"
  • 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. – Ali Razeghi Nov 29 '18 at 19:09
  • 1
    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 – billinkc Nov 29 '18 at 19:09
  • 5
    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. – Aaron Bertrand Nov 29 '18 at 19:14
  • I'm confused... – Jennifer Nov 29 '18 at 19:21
  • 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. – Ali Razeghi Nov 29 '18 at 19:57
5

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.).

  • 2
    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. – db2 Nov 29 '18 at 20:37
1

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.

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.