SQL Drop tables (mistaken) from master db

When I deploy the scripts to SQL server I have have a error and deployment was failed. All tables were created to master db and I wrote below script to remove all of them.

Uncomment to execute; –EXEC sp_executesql @str

DECLARE @str NVARCHAR(MAX)
SET @str = ”

/****DROP FOREIGN KEYS***/
WHILE ( EXISTS ( SELECT 1
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  CONSTRAINT_TYPE = ‘FOREIGN KEY’
AND table_catalog = ‘master’
AND Table_name NOT IN ( ‘MSreplication_options’,
‘spt_fallback_db’,
‘spt_fallback_dev’,
‘spt_fallback_usg’,
‘spt_monitor’, ‘spt_values’ ) ) )
BEGIN
DECLARE @sql NVARCHAR(2000)
SELECT TOP 1
@sql = ( ‘ALTER TABLE ‘ + TABLE_SCHEMA + ‘.[‘ + TABLE_NAME
+ ‘] DROP CONSTRAINT [‘ + CONSTRAINT_NAME + ‘]’ )
FROM    information_schema.table_constraints
WHERE   CONSTRAINT_TYPE = ‘FOREIGN KEY’
EXEC (@sql)
END
/*******/

SELECT  table_name ,
ROW_NUMBER() OVER ( ORDER BY table_name ) AS Rn
INTO    #tempTable
FROM    master.information_schema.tables
WHERE   Table_name NOT IN ( ‘MSreplication_options’, ‘spt_fallback_db’,
‘spt_fallback_dev’, ‘spt_fallback_usg’,
‘spt_monitor’, ‘spt_values’ )

DECLARE @COunt INT
SET @COunt = 0 ;

SELECT  @COunt = COUNT(*)
FROM    #tempTable

DECLARE @TableName VARCHAR(50)
WHILE @COunt > 0
BEGIN
SELECT  @TableName = table_name
FROM    #tempTable
WHERE   Rn = @COunt
SET @str = ‘Drop Table ‘ + @TableName + ‘;’
PRINT @str

–now to drop table uncomment below line
–EXEC sp_executesql @str
SET @COunt = @COunt – 1 ;
END

/*DROP temp DB*/
DROP TABLE #tempTable

This entry was posted in SQL. Bookmark the permalink.

Leave a comment