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.