Create dynamic view

Question

I create the DB each month: For example: DB_202001. Date is included in the dbname. Here is a script about view. How to modify the view and let it only select dbs within 1 year?

For example：DB_201910 to DB_202009

ALTER VIEW [dbo].[DBHIST] as (
select * from (
(select * from [DB_202001].[dbo].[DBHIST]) union all
(select * from [DB_202002].[dbo].[DBHIST]) union all
(select * from [DB_202003].[dbo].[DBHIST]) union all
(select * from [DB_202004].[dbo].[DBHIST]) union all
(select * from [DB_202005].[dbo].[DBHIST]) union all
(select * from [DB_202006].[dbo].[DBHIST]) union all
(select * from [DB_202007].[dbo].[DBHIST])union all
(select * from [DB_202008].[dbo].[DBHIST])union all
(select * from [DB_202009].[dbo].[DBHIST]) union all
(select * from [DB_202010].[dbo].[DBHIST]) union all
(select * from [DB_202011].[dbo].[DBHIST]) union all
(select * from [DB_202012].[dbo].[DBHIST]) union all
(select * from [DB_202101].[dbo].[DBHIST]) union all
(select * from [DB_202102].[dbo].[DBHIST]) union all
(select * from [DB_202103].[dbo].[DBHIST]) union all
(select * from [DB_202104].[dbo].[DBHIST]) union all
(select * from [DB_202105].[dbo].[DBHIST]) union all
(select * from [DB_202106].[dbo].[DBHIST]) union all
(select * from [DB_202107].[dbo].[DBHIST]) union all
(select * from [DB_202108].[dbo].[DBHIST]) union all
(select * from [DB_202109].[dbo].[DBHIST])

) as x

)

Answer 1

Hi @RuoRuo ,

Is it possible to query all DBs in the server and put these dbs in the Alter View command automatically？

Please refer below and check whether it is helpful.

declare @sql nvarchar(max)=''  
  
set @sql = N' ALTER VIEW [dbo].[DBHISTVIEW] as (  
select * from ('  
  
SELECT @sql =@sql+  STUFF(( SELECT ' (select * from ' + QUOTENAME(NAME)+'.[dbo].[DBHIST]) union all '   
FROM sys.databases   
WHERE database_id > 4   --exclude all system database  
and NAME like 'db_20%' --filter all database start with db_20.  
FOR XML PATH('') ), 1, 1, '')   
  
SET @sql=SUBSTRING(@SQL,1,LEN(@SQL)-LEN('union all'))+ ') as x)'  
--print @sql   
EXEC(@sql)  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @RuoRuo ,

Welcome to Microsoft Q&A!

I changed the name of your view from '[dbo].[DBHIST]' to '[dbo].[DBHISTVIEW]' due to the error 'There is already an object named 'DBHIST' in the database.' reported when I created your view.

Please refer below and check whether it is working.

DECLARE @DBNAME DATE=DATEADD(MONTH,-11,getdate())  
   
declare @sql nvarchar(max)=''  
  
set @sql = N' ALTER VIEW [dbo].[DBHISTVIEW] as (  
select * from ('  
  
WHILE @DBNAME<=CAST(getdate() AS DATE)  
BEGIN  
   SET @sql=@sql+' (select * from [DB_'+FORMAT(@DBNAME,'yyyyMM')+'].[dbo].[DBHIST]) union all'  
   SET @DBNAME=DATEADD(MONTH,1,@DBNAME)  
END  
  
SET @sql=SUBSTRING(@SQL,1,LEN(@SQL)-LEN('union all'))+ ') as x)'  

--print @sql   
EXEC(@sql)  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

It is not possible to have a view, which dynamically selects data from databases with varying names.

Why that design with one database per month? I would put all data in one database/one table; SQL Server scales well.

Answer 4

In the job where you create this database, you will need to add code that re-creates the view with help of dynamic SQL.

I will have to say that creating a new database every month sounds a bit excessive. Any special reason for this?

Answer 5

Thanks Melissa.

I have the other requirement. Is it possible to query all DBs in the server and put these dbs in the Alter View command automatically？