Sql-server – Run Querys on List of Databases (For Each loop?)

I have a selection of about 15 databases on an SQL server that I want to run some query's on. I do not want to affect any of the roughly 10 other databases.

Specifically, I want to run the following commands:

USE MASTER;
GO


ALTER DATABASE DATABASENAME
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO


ALTER DATABASE DATABASENAME
SET OFFLINE
WITH ROLLBACK IMMEDIATE;
GO


EXEC MASTER.dbo.sp_detach_db @dbname = N'DATABASENAME'
GO

My goal is to take these databases offline, detach them, move the .mdf and .ldf files to a different disk, then re-attach them using the GUI.

In an ideal world, I would also love to be able to then run the following command on them after all have been re-attached:

ALTER DATABASE databasename SET ENABLE_BROKER

In a perfect world, I would be able to designate a list of databases and then loop through all of the commands.

Something PowerShell-y would look like this:

$Databasesnames = @("Database1","Database2","Database3")
ForEach ($Databasename in $Databasenames)
{
USE MASTER;
GO


-- Take database in single user mode
ALTER DATABASE $Databasename
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO


--Take databace offline
ALTER DATABASE $Databasename
SET OFFLINE
WITH ROLLBACK IMMEDIATE;
GO


-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'$Databasename'
GO
}

And then after something like

$Databasesnames = @("Database1","Database2","Database3")
ForEach ($Databasename in $Databasenames)
{
ALTER DATABASE $Databasename SET ENABLE_BROKER
}

I tried this T-SQL ‘while loop approxmiation’ without any luck:

--Set Base Table for Loop Action, Add Database Names
USE TEMPDB
GO
CREATE TABLE #DBS
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #DBS ( Id, Name, Status)
Values (1, 'Database1', 0),
        (2, 'Database2', 0),
        (3, 'Database3', 0)
GO


--Loop through DB names, taking them offline and detaching them. 
DECLARE @LoopCounter INT = 1, @MaxDBId INT = 3 , 
        @DBName NVARCHAR(100)


WHILE(@LoopCounter <= @MaxDBId)
BEGIN
   SELECT @DBName = Name
   FROM #DBS WHERE Id = @LoopCounter
USE MASTER
GO
-- Take database in single user mode
ALTER DATABASE @DBName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO


--Take DB Offline
ALTER DATABASE @DBName SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO


-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname
GO


   SET @LoopCounter  = @LoopCounter  + 1        
END

UPDATE 1
Here is my revised script

--Declare base Dynamic SQL Variables

DECLARE @sql nvarchar(max);

DECLARE @sql2 nvarchar(max);


--Set Base Table for Loop Action, Add Database Names
USE TEMPDB
GO
CREATE TABLE #DBS
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #DBS ( Id, Name, Status)
Values (1, 'Database1', 0),
        (2, 'Database2', 0),
        (3, 'Database3', 0)
GO


--Loop through DB names, taking them offline and detaching them. 
DECLARE @LoopCounter INT = 1, @MaxDBId INT = 3 , 
        @DBName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxDBId)
BEGIN
   SELECT @DBName = Name
   FROM #DBS WHERE Id = @LoopCounter
USE MASTER
GO

 SET @sql = N'ALTER DATABASE ' + QUOTENAME(@DBName) 
    + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';

 SET @sql2 = N'ALTER DATABASE ' + QUOTENAME(@DBName) 
    + N' SET OFFLINE WITH ROLLBACK IMMEDIATE;';

EXEC sys.sp_executesql @sql;

EXEC sys.sp_executesql @sql2;

EXEC MASTER.dbo.sp_detach_db @dbname;
GO


SET @LoopCounter  = @LoopCounter  + 1


END