Create dynamic view

RuoRuo 41 Reputation points
2021-09-22T10:55:35.937+00:00

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

)

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,186 Reputation points
    2021-09-24T05:48:35.223+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,186 Reputation points
    2021-09-23T03:14:42.437+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 43,081 Reputation points
    2021-09-22T13:13:31.823+00:00

    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.

    0 comments No comments

  3. Erland Sommarskog 106.2K Reputation points MVP
    2021-09-22T22:19:43.73+00:00

    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?

    0 comments No comments

  4. RuoRuo 41 Reputation points
    2021-09-24T05:33:07.98+00:00

    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?

    0 comments No comments