Create a table in all the databases in SQL Server

Suppose we get the requirement that we need to create a table in all database on the server. If we have small number of databases in server we can connect each database and run the create table script and create it, but there are more database like 100+ database then it is very difficult to create. Using below script will create a table in entire database.

Let’s see the example.

I want to create Audit table in all database on the server.

DECLARE @DDL VARCHAR(MAX) SET @DDL='Create table Audit(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

Before running the script.

 

After running the script