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
"
"

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 - AWS
– Ali Razeghi - AWS, Commented Nov 29, 2018 at 19:09
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
– billinkc, Commented Nov 29, 2018 at 19:09
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
– Aaron Bertrand, Commented Nov 29, 2018 at 19:14
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 - AWS
– Ali Razeghi - AWS, Commented Nov 29, 2018 at 19:57

Andriy M · Accepted Answer · 2018-11-29 19:42:52Z

6

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

answered Nov 29, 2018 at 19:42

Andriy M

23.3k6 gold badges60 silver badges104 bronze badges

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
– db2

2018-11-29 20:37:55 +00:00
Commented Nov 29, 2018 at 20:37

Add a comment |

Aaron Bertrand · Accepted Answer · 2018-11-29 20:06:31Z

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:

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

Stack Exchange Network

Create a View from Multiple Databases

2 Answers 2

Your Answer

Hot Network Questions

Create a View from Multiple Databases

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions