Dropping All Tables, SPs, FNs, and Views

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