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

1 comment:

  1. BSNL customer care number of each state regarding to BSNL services towards toll free and paid numbers which are accessed from own and other networks across the country. BSNL Customer Care number The new digital era based support system contributing a lion’s sharing related to all telecom services in 2021 calendar year, also find the new email address providing BSNL complaint process for payment failed issues for any digital payment service failure related issues like repayment, refund of debited amount.

    ReplyDelete