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

It is extremely poor database design to create a database per month. It make it extremely difficult to manage over time and to query multiple months.

What is your purpose for doing this?