CODEWITHSUNDEEP
sqlsql-server
ThrowawayProg
ThrowawayProg

Reputation: 3

How to create a dynamic view that reads from a table?

I have a server with sister databases. I currently have the following view that gets data from these databases

SELECT UnId FROM [DatabaseOne].[dbo].Customers
UNION ALL
SELECT UnId FROM [DatabaseTwo].[dbo].Customers
UNION ALL
SELECT UnId FROM [DatabaseThree].[dbo].Customers

However I often need to add or remove databases, so a static view doesn't seem like the best solution. I already have a table that contains all of the sister databases, as showcased below.

DATDB

UnId | DSTABLE
1    | DatabaseOne
2    | DatabaseTwo
3    | DatabaseThree

How can I create a dynamic view that reads this table?

Upvotes: 0

Views: 57

Answers (2)

mr R
mr R

Reputation: 1126

Consider stored procedure that read DATADB table and create SELECT syntax for example and run it as dynamic SQL

declare @SQL varchar(1000)
declare @table as table (Unid int, DSTABLE varchar(50))
INSERT INTO @table
select 1 as Unid, 'DatabaseOne' as DSTABLE union
select 2 as Unid, 'DatabaseTwo' as DSTABLE union
select 3 as Unid, 'DatabaseThree' as DSTABLE 



 SELECT @SQL = (STUFF((
        SELECT ' ' + ' SELECT Unid FROM ' + QUOTENAME(DSTABLE) + '.[dbo].[Customers] UNION ALL'
        FROM @table
        FOR XML PATH('')
        ), 1, 2, '')
    ) -- Multiple rows to one row

SELECT @SQL = SUBSTRING(@SQL, 0, LEN(@SQL)-9) -- remove last UNION ALL
--exec (@SQL)
--print @SQL

Output

SELECT Unid FROM [DatabaseOne].[dbo].[Customers] UNION ALL  
SELECT Unid FROM [DatabaseTwo].[dbo].[Customers] UNION ALL  
SELECT Unid FROM [DatabaseThree].[dbo].[Customers]

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31775

As Sean clearly states, a view cannot be dynamic.

Something else you can do, besides Sam's comment, is put a TRIGGER on your DATDB table, AFTER INSERT/UPDATE/DELETE that builds and executes a ALTER VIEW statement to change the view to select from the latest values in the DATDB table.

Upvotes: 2

Related Questions