USE {DATABASE_NAME}
GO
——————————————————–
— Drop Procedures
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘P’
OPEN obj_cur
FETCH obj_cur INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP PROCEDURE ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END
CLOSE obj_cur
DEALLOCATE obj_cur
GO
——————————————————–
— Drop Functions
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type IN (‘FN’,’IF’,’TF’)
OPEN obj_cur
FETCH obj_cur INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP FUNCTION ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END
CLOSE obj_cur
DEALLOCATE obj_cur
GO
——————————————————–
— Drop Views
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘V’
OPEN obj_cur
FETCH obj_cur INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP VIEW ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END
CLOSE obj_cur
DEALLOCATE obj_cur
GO
——————————————————–
— Drop Constraints
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE @tabname sysname
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT A.name [const_name]
, B.name [table_name]
FROM sysobjects A
, sysobjects B
, sysconstraints C
WHERE A.id = C.constid
AND B.id = C.id
AND B.name NOT LIKE ‘sys%’
AND A.type IN ( ‘F’, ‘K’ )
ORDER BY A.type, 2 DESC, 1
OPEN obj_cur
FETCH obj_cur INTO @objname, @tabname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER TABLE ‘ + @tabname + ‘ NOCHECK CONSTRAINT ‘ + @objname
PRINT @sql
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname, @tabname
END
CLOSE obj_cur
OPEN obj_cur
FETCH obj_cur INTO @objname, @tabname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘ALTER TABLE ‘ + @tabname + ‘ DROP CONSTRAINT ‘ + @objname
PRINT @sql
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname, @tabname
END
CLOSE obj_cur
DEALLOCATE obj_cur
GO
——————————————————–
— Drop Tables
——————————————————–
DECLARE @objname sysname
DECLARE @sql nvarchar(max)
DECLARE obj_cur CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE type = ‘U’
OPEN obj_cur
FETCH obj_cur INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘DROP TABLE ‘ + @objname
EXEC sp_executesql @sql
FETCH obj_cur INTO @objname
END
CLOSE obj_cur
DEALLOCATE obj_cur
GO