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.
  1. BEGIN TRAN
  3. CREATE TABLE #TempGETDBLIST
  4. (
  5. [NAME] NVARCHAR(255) NULL,
  6. [ROWID] INT IDENTITY NOT NULL
  7. )
  8. INSERT INTO #TempGETDBLIST (NAME)
  10. --unomment which database use want to apply
  11. --SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%EnterpriseGL%' -- get all GL databases
  12. --SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%GR8%' --get all GR8 databases
  14. SELECT '#TempGETDBLIST',* FROM #TempGETDBLIST
  15. DECLARE @Flag INT = 1
  17. WHILE (@Flag <= (SELECT COUNT(1) FROM #TempGETDBLIST))
  18. BEGIN
  19. DECLARE @ABC NVARCHAR(50), @query NVARCHAR(max)
  20. SET @ABC =(SELECT Name FROM #TempGETDBLIST WHERE ROWID = @Flag)
  21. SET @query = 'USE '+ @ABC +' select top 1 * from SystemConfiguration'
  23. EXECUTE( @query )
  24. SET @Flag = @Flag + 1
  26. END
  28. DROP TABLE #TempGETDBLIST
  29. ROLLBACK TRAN