question

RuoRuo-5318 avatar image
0 Votes"
RuoRuo-5318 asked TomPhillips-1744 answered

Create dynamic view

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

)

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RuoRuo-5318,

Could you please validate the latest answer and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @RuoRuo-5318,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @RuoRuo-5318,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RuoRuo-5318 avatar image
0 Votes"
RuoRuo-5318 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.