How to create View in all the databases in SQL Server

Scenario: How to create a view in all SQL Server Databases

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.


--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