April 17, 2020 07:12 by
Peter
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
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.