-- Query to delete all the Stored Procedure, Function (Scalar and Table Value Function) and User Defined Tables within the database

DECLARE @Cnt as INTEGER
DECLARE @SPName as varchar(100)
DECLARE @FuncName as varchar(100)
DECLARE @TableName as varchar(100)
DECLARE @Qry as varchar(200)

-- Stored Procedure

select @Cnt = Count(id) from sysobjects where type = 'p'

WHILE (@Cnt > 0)
BEGIN
SELECT @SPName = name From
(SELECT row_number() over (order by name) as rownumber, name from Sysobjects where type = 'p') t WHERE rownumber = @Cnt
exec ('DROP Procedure ' + @SPName)
SET @Cnt = @Cnt - 1
END

--Table Valued Function

select @Cnt = Count(id) from sysobjects where type = 'TF'
WHILE (@Cnt > 0)
BEGIN
SELECT @FuncName = name From
(SELECT row_number() over (order by name) as rownumber, name from Sysobjects where type = 'TF') t
WHERE rownumber = @Cnt
exec ('DROP Function ' + @FuncName)
SET @Cnt = @Cnt - 1
END

--Scalar valued Function

select @Cnt = Count(id) from sysobjects where type = 'FN'
WHILE (@Cnt > 0)
BEGIN
SELECT @FuncName = name From
(SELECT row_number() over (order by name) as rownumber, name from Sysobjects where type = 'FN') t
WHERE rownumber = @Cnt
exec ('DROP Function ' + @FuncName)
SET @Cnt = @Cnt - 1
END

-- Tables

SELECT @Cnt = Count(id) from sysobjects where type = 'u'
WHILE (@Cnt > 0)
BEGIN
SELECT @tablename = name FROM
(SELECT row_number() over (order by name) as rownumber, name from Sysobjects where type = 'u') t
WHERE rownumber = @Cnt
--To Delete all the Constraints for the Table
IF(SELECT Count(Constraint_Name) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_Name = @tablename) > 0
BEGIN
SELECT @Qry = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name = @tablename
EXEC sp_executesql @Qry
END
--To Delete all the Records in the Table
EXEC ('Truncate Table ' + @tablename)
--To Delete the Table
EXEC ('Drop Table ' + @tablename)
SET @Cnt = @Cnt - 1
END