How to create table in all the databases in SQL Server - SQL Server Scripts

Scenario:

You are working as SQL Server DBA or developer and you got this requirement" You have to create a table in all the databases on SQL Server Instance" If you have few databases, it might be each to change the Database scope in session and run the query but think about situation if you have 100's of databases and you have to create the table in every database.

Below script can be used to create table in all the Databases on SQL server. You can also filter the databases on which you would like to run the script. Please the DDL statement for your table create in below script and you are good to go.

--Provide the DDL Statment that you would like to run for Create table in Each Databsae in SQL Server
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL='Create table dbo.MyNewTableName (id int, name varchar(100))'
    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(500);

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

        --USE Dynamic SQL To Change DB name and run DDL statement to create table
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

Once you run above script, dbo.MyNewTableName will be created in all the database which were returned by select * from sys.databases.