Apply a Single Script for Multiple Databases Using Database Name

In this blog, I will explain to you how to apply a script or a query to multiple databases in a single execution using a database name.

Introduction 

 

Hi all, In this blog, I will explain to you how to apply a script or a query to multiple databases in a single execution using a database name. I get all database names, create a loop, and execute a command that I want to do.

 

Here I am using 2 databases, EnterpriseGL and GR8. I get all database names using a loop, and after that, I get database names one by one, put a script with a name, and execute the script. 

BEGIN TRAN

CREATE TABLE #TempGETDBLIST
   (
      [NAME] NVARCHAR(255) NULL,
      [ROWID] INT IDENTITY NOT NULL
   )
         INSERT INTO #TempGETDBLIST (NAME)

      --unomment which database use want to apply
     --SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%EnterpriseGL%'  -- get all GL databases
      --SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%GR8%' --get all GR8 databases

        SELECT '#TempGETDBLIST',* FROM #TempGETDBLIST
         DECLARE @Flag INT = 1

WHILE (@Flag <= (SELECT COUNT(1) FROM #TempGETDBLIST))
BEGIN
      DECLARE @ABC NVARCHAR(50), @query NVARCHAR(max)
      SET @ABC =(SELECT Name FROM #TempGETDBLIST WHERE ROWID = @Flag)
      SET @query = 'USE '+ @ABC +' select top 1 * from SystemConfiguration'

   EXECUTE( @query )
   SET @Flag = @Flag + 1

END

DROP TABLE #TempGETDBLIST
ROLLBACK TRAN