Attempting to change databases using cursor loop, but database won't change

Question

I'm writing a script that, when executed, will drop any tables in a list of given DBs if the DB Name + Table Name combo does not exist in a table named CleanUpTableList. All DBs reside on the same server. I am using SQL Server 2014.

I am trying to do this by creating an outer cursor loop which cycles through the list of DB names and an inner cursor loop which pulls in a list of table names within the given database which are not found in CleanUpTableList and drops those tables. However, it seems that the outer loop fails to change databases. The script will only access the relevant tables of the starting database X times, with X being however many database name entries there are in the outer cursor. So, for example, if I start in Database1, and I have three database name entries in my outer cursor, instead of getting:

DROP TABLE Database1..TableB
DROP TABLE Database1..TableC
DROP TABLE Database2..TableE
DROP TABLE Database2..TableF
DROP TABLE Database3..TableH
DROP TABLE Database3..TableI

I get:

DROP TABLE Database1..TableB
DROP TABLE Database1..TableC
DROP TABLE Database1..TableB
DROP TABLE Database1..TableC
DROP TABLE Database1..TableB
DROP TABLE Database1..TableC

...Which is not really what I want, so I am assuming something is amiss in the outer loop. I know the usual DB change command is

USE Database1;
GO

But I wasn't able to figure out how to do that with EXEC(). It kept telling me there was a syntax error near GO, I assume because GO can't be on the same line as 'USE Database1;', and I don't know how make a new line when using EXEC(). I tried using

SET @ChangeDB = 'USE ' + @DatabaseName + ';'
EXEC(@ChangeDB + CHAR(13) + 'GO') 

and

SET @ChangeDB ='USE ' + @DatabaseName  + ';' +CHAR(13) + 'GO'
EXEC(@ChangeDB)

but these also returned a syntax error.

Here is the relevant code:

DB/Table Creation Script:

CREATE DATABASE Database1;
CREATE DATABASE Database2;
CREATE DATABASE Database3;
CREATE DATABASE Database4;

CREATE TABLE Database1.dbo.TableA (Column1 INT, Column2 INT);
CREATE TABLE Database1.dbo.TableB (Column1 INT, Column2 INT);
CREATE TABLE Database1.dbo.TableC (Column1 INT, Column2 INT);

CREATE TABLE Database2.dbo.TableD (Column1 INT, Column2 INT);
CREATE TABLE Database2.dbo.TableE (Column1 INT, Column2 INT);
CREATE TABLE Database2.dbo.TableF (Column1 INT, Column2 INT);

CREATE TABLE Database3.dbo.TableG (Column1 INT, Column2 INT);
CREATE TABLE Database3.dbo.TableH (Column1 INT, Column2 INT);
CREATE TABLE Database3.dbo.TableI (Column1 INT, Column2 INT);

CREATE TABLE Database4.dbo.CleanUpTableList (DBName VARCHAR(20), TableName VARCHAR(20));

INSERT INTO Database4..CleanUpTableList VALUES ('Database1','TableA')
INSERT INTO Database4..CleanUpTableList VALUES ('Database2','TableD')
INSERT INTO Database4..CleanUpTableList VALUES ('Database3', 'TableG')

Clean Up Script:

DECLARE @fetch_database_cursor INT
DECLARE @DatabaseName VARCHAR(50)

DECLARE DatabaseList CURSOR FOR 

    select name from sys.databases
    where
    name IN ('Database1','Database2', 'Database3'
            )   

OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DatabaseName

/* Keep track of the outer loop FETCH_STATUS in a local variable */

SET @fetch_database_cursor = @@FETCH_STATUS

/* Use outer loop FETCH_STATUS local variable as condition for outer WHILE  loop */

WHILE @fetch_database_cursor = 0
BEGIN

    DECLARE @ChangeDB VARCHAR(2500)
    DECLARE @TableName VARCHAR(50)
    DECLARE @ExecuteSQL VARCHAR(2500)
    DECLARE @fetch_table_cursor INT

    /* Change DB here */

    SET @ChangeDB = 'USE ' + @DatabaseName
    EXEC(@ChangeDB)

    /* Declare inner cursor */

    DECLARE TableList CURSOR FOR
        select table_name
        from information_schema.tables
        WHERE TABLE_TYPE = 'BASE TABLE'
        AND table_name NOT IN (
            SELECT TableName
            FROM Database4..CleanUpTableList
            WHERE DBName = @DatabaseName
            )
        ORDER BY table_name

    OPEN TableList
    FETCH NEXT FROM TableList INTO @TableName

    /* Store inner cursor fetch_status in local variable */

    SET @fetch_table_cursor = @@FETCH_STATUS

    /* Use inner cursor fetch_status local variable as condition for inner WHILE loop */

    WHILE @fetch_table_cursor = 0

        BEGIN
            SET @ExecuteSQL = 'DROP TABLE ' +@Tablename
            EXEC(@ExecuteSQL)
            SELECT @Tablename, 'Has Been Successfully Dropped'
            FETCH NEXT FROM TableList INTO @TableName
            SET @fetch_table_cursor=@@FETCH_STATUS

        END

    /* Close and deallocate inner cursor */

    CLOSE TableList
    DEALLOCATE TableList

    FETCH NEXT FROM DatabaseList INTO @DatabaseName
    SET @fetch_database_cursor = @@FETCH_STATUS
END

/* Close and deallocate outer cursor */

CLOSE DatabaseList
DEALLOCATE DatabaseList

Any suggestions are appreciated.

Answer 1

From Your code ,i understood that you are trying to do same operation in all databases,that can be implemented by sp_msforeachdb..

--all databases

 EXECUTE master.sys.sp_MSforeachdb 
 'USE [?];
 if db_id()<=4 return;
drop table dbo.sometable'

--run only few databases..

EXECUTE master.sys.sp_MSforeachdb 
'USE [?]; 
if db_name(db_id()) in (''master'',''tempdb'') --your dbnames
Begin 
select db_name() --your query
end'

You could also use Aaron Bertrand's rewrite of Sp_msforeachDB which also can deal with some limitations of Sp_msforeachdb :Making a more reliable and flexible sp_MSforeachdb