September 20, 2016 at 10:10 am
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.
September 20, 2016 at 10:33 am
When using dynamic SQL, you have to be aware of the scope. You're changing the database inside your dynamic SQL, and that change is limited to the scope of the dynamic SQL. Once your dynamic SQL is finished, it reverts to the previous scope, that is, it reverts to your original database. Anything that requires that it operate within the scope of database change, needs to be included in your dynamic SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 20, 2016 at 10:43 am
Hmm, I think I understand. So I would need to include the whole inner cursor loop inside @CreateDB and then execute it in order for it to work as I intend it to?
September 20, 2016 at 11:32 am
For this, you can leverage sp_MsForEachDB. It will execute the specified batch of T-SQL across all databases. For each iteration, the token ? will be dynamically substituted with the database name.
In the example below, we are dropping Tables A, B, and C across databases 1, 2, and 3.
EXEC SP_MSFOREACHDB '
USE ?;
IF DB_NAME() in (''Database1'',''Database2'',''Database3'')
BEGIN;
DROP TABLE TableA;
DROP TABLE TableB;
DROP TABLE TableC;
END;
';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 20, 2016 at 12:19 pm
This worked great, thanks!
September 20, 2016 at 1:10 pm
Here's an alternative that doesn't rely on undocumented procedures.
DECLARE @SQL nvarchar(MAX) = '';
SELECT @SQL = (SELECT 'DROP TABLE ' + QUOTENAME( DBName) + '..' + QUOTENAME(TableName) + ';' + CHAR(10)
FROM CleanUpTableList
WHERE OBJECT_ID(QUOTENAME( DBName) + '..' + QUOTENAME(TableName)) IS NOT NULL
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)');
EXECUTE sp_executesql @SQL;
The variable assignment can be changed to this, but it might be slightly slower:
DECLARE @SQL nvarchar(MAX) = '';
SELECT @SQL = @SQL + 'DROP TABLE ' + QUOTENAME( DBName) + '..' + QUOTENAME(TableName) + ';' + CHAR(10)
FROM CleanUpTableList
WHERE OBJECT_ID(QUOTENAME( DBName) + '..' + QUOTENAME(TableName)) IS NOT NULL;
September 20, 2016 at 1:24 pm
Please, disregard my previous post as it does the opposite of what you asked.
Here's a corrected code:
DECLARE @DatabaseName NVARCHAR(130)
DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
select QUOTENAME(name)
from sys.databases
where name IN ('Database1','Database2', 'Database3')
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (N' USE ' + @DatabaseName + ';
DECLARE @DROPSQL nvarchar(MAX),
@BaseSQL nvarchar( 300) = ''DROP TABLE <<DBName>>..<<TableName>>;'' + CHAR(10);
SELECT @DROPSQL = (SELECT REPLACE( REPLACE( @BaseSQL, ''<<DBName>>'', QUOTENAME(DB_NAME())), ''<<TableName>>'', QUOTENAME(t.name))
FROM sys.tables t
WHERE NOT EXISTS( SELECT 1
FROM Database4.dbo.CleanUpTableList cu
WHERE cu.DBName = DB_NAME()
AND t.name = cu.TableName)
FOR XML PATH(''''), TYPE).value(''./text()[1]'', ''nvarchar(max)'');
EXECUTE sp_executesql @DROPSQL;
');
FETCH NEXT FROM DatabaseList INTO @DatabaseName;
END;
CLOSE DatabaseList;
DEALLOCATE DatabaseList;
September 20, 2016 at 2:05 pm
Luis Cazares (9/20/2016)
Here's an alternative that doesn't rely on undocumented procedures....
Yeah, but Microsoft doesn't officially support your method either. :satisfied:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 20, 2016 at 2:18 pm
Eric M Russell (9/20/2016)
Luis Cazares (9/20/2016)
Here's an alternative that doesn't rely on undocumented procedures....
Yeah, but Microsoft doesn't officially support your method either. :satisfied:
Doesn't support or doesn't have documentation on this usage? It's not the same thing. The method is completely documented, just not as a concatenation solution.
September 20, 2016 at 2:43 pm
Luis Cazares (9/20/2016)
Eric M Russell (9/20/2016)
Luis Cazares (9/20/2016)
Here's an alternative that doesn't rely on undocumented procedures....
Yeah, but Microsoft doesn't officially support your method either. :satisfied:
Doesn't support or doesn't have documentation on this usage? It's not the same thing. The method is completely documented, just not as a concatenation solution.
Yes, I'm talking about documentation. The procedure sp_MsForEachDatabase is well documented by the SQL Server user community. It's OK for one-off maintenance tasks like this one appears to be.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply