SQL Server: Tips and Tricks - 2 (2017)

How to create View in all the databases in SQL Server

Scenario:

You are working as SQL Server DBA or SQL Server developer and you need to create a view with same definition in multiple databases of all databases on SQL Server Instance. You can use below script to create view in multiple databases. I used cursor to loop through the list of databases. You can always filter (choose) the databases in select * from sys.databases query to create view in required databases.

Solution:

--Provide the DDL Statment that you would like to run for Create View in Each Databsae in SQL Server

DECLARE @DDL VARCHAR(MAX)

SET @DDL=

'Create View dbo.MyTestView

AS

Select * from dbo.MyNewTableName'

DECLARE @DatabaseName AS VARCHAR(500)

--DECLARE CURSOR

DECLARE CUR CURSOR

FOR

--Choose the DBs on which you would like to run the script

SELECT NAME

FROM sys.databases

WHERE database_id > 4

--OPEN CURSOR

OPEN CUR

--NEXT NEXT RECORD

FETCH NEXT

FROM CUR

INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @DBName AS NVARCHAR(128);

SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

--USE Dynamic SQL To Change DB name and run DDL statement to create View

EXEC (

N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'

);

FETCH NEXT

FROM CUR

INTO @DatabaseName

END

CLOSE CUR

DEALLOCATE CUR