Use test
--Delete all (constraint F)
DECLARE @countF INT
SET @countF = (SELECT COUNT (*) from test.sys.objects WHERE type = ' F ')
PRINT ' A total of ' + CAST (@countF as VARCHAR (5)) + ' constraint. ‘
While @countF >0
BEGIN
DECLARE @F_Name VARCHAR (50)
DECLARE @tbName VARCHAR (50)
DECLARE @Cmd VARCHAR (100)
DECLARE @parent_Object_ID INT
SET @F_Name = (SELECT TOP (1) Name from test.sys.objects
WHERE name in (SELECT t.name from test.sys.objects as [t]) and type= ' F '
)
SET @parent_Object_ID = (SELECT parent_object_id from test.sys.objects WHERE [email protected]_name)
SET @tbName = (SELECT name from Test.sys.tables WHERE [email protected]_object_id)
SELECT @Cmd = ' ALTER TABLE ' + @tbName + ' DROP constraint ' + @F_Name
PRINT @Cmd
EXEC (@Cmd)
SET @[email protected]
END
--Delete all the tables
DECLARE @temp INT
SET @temp = (SELECT COUNT (*) from Sys.tables)
PRINT ' A total of ' + CAST (@temp as VARCHAR (20)) + ' table. ‘
While @temp >0
BEGIN
DECLARE @tbName VARCHAR (50)
SET @tbName = (select TOP (1) name from Sys.tables WHERE name in (select T.name from sys.tables as [t]))
EXEC (' DROP TABLE ' + @tbName)
PRINT ' table: ' + @tbName + ' Delete succeeded! ‘
SET @[email protected]
END
Delete all tables below a database