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