USE master
go
-- ** BEGIN ** 
-- If Shiloh, then create as system SP for the ANSI views to function correctly
IF CHARINDEX( '8.00' , @@version ) > 0
BEGIN
        EXEC sp_configure 'allow updates' , 1
        EXEC sp_MS_upd_sysobj_category 1
END
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_drop_all_objects'), 'IsProcedure') = 1 
BEGIN 
    DROP PROCEDURE dbo.sp_drop_all_objects 
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_drop_all_objects'), 'IsProcedure') = 1 
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_drop_all_objects >>>' 
    ELSE 
        PRINT '<<< DROPPED PROCEDURE dbo.sp_drop_all_objects >>>' 
END 
go 
CREATE PROCEDURE sp_drop_all_objects (
        @JustCheck bit = 1
)
--WITH ENCRYPTION 
AS 
/********************************************************************************* 
 * $History: $ 
 *********************************************************************************/ 
DECLARE @TableOwner nvarchar( 128 ) , @TableName nvarchar( 262 ) , @TableID int ,
        @FullTextColName nvarchar( 128 ) , @FullTextColID int , @FullTextDocTypeColName nvarchar( 128 ) ,
        @FullTextDocTypeColID int , @FullTextLanguage nvarchar( 128 ) , @FullTextKeyIndexName nvarchar( 128 ) ,
        @FullTextKeyColID int , @FullTextIndexActive int , @FullTextCatalogName nvarchar( 128 ) ,
        @FullTextCatID int , @FullTextCatPath nvarchar( 260 ) , @FullTextStatus int , @FullTextNumTables int ,
        @FullTextCols CURSOR , @FullTextTables CURSOR , @FullTextCatalogs CURSOR ,
        @RoutineName nvarchar( 262 ) , @ConstraintName nvarchar( 130 ) ,
        @DomainConstraintName nvarchar( 128 ) , @DomainName nvarchar( 128 ) ,
        @TableType varchar( 10 ) , @RoutineType nvarchar( 20 ) , @CmdStr nvarchar( 4000 )

SET @JustCheck = COALESCE( @JustCheck , 1 )

-- Cleanup full-text settings if enabled
IF DATABASEPROPERTY( DB_NAME() , 'IsFulltextEnabled' ) = 1
BEGIN
        -- Drop fulltext columns
        EXEC sp_help_fulltext_columns_cursor @FullTextCols OUTPUT
        WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
        BEGIN
                FETCH NEXT FROM @FullTextCols INTO @TableOwner , @TableID , @TableName ,
                                                   @FullTextColName , @FullTextColID , 
                                                   @FullTextDocTypeColName , @FullTextDocTypeColID ,
                                                   @FullTextLanguage
                IF @@FETCH_STATUS < 0 BREAK
                SET @CmdStr = N'EXEC sp_fulltext_column ''' + QUOTENAME( @TableOwner ) + N'.' +
                              QUOTENAME( @TableName ) + N''' , ' + QUOTENAME( @FullTextColName ) + N' , ''drop'''
                IF @JustCheck = 1
                        PRINT @CmdStr
                ELSE
                        EXECUTE( @CmdStr )
        END
        DEALLOCATE @FullTextCols

        -- Drop fulltext tables
        EXEC sp_help_fulltext_tables_cursor @FullTextTables OUTPUT
        WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
        BEGIN
                FETCH NEXT FROM @FullTextTables INTO @TableOwner , @TableName , @FullTextKeyIndexName ,
                                                     @FullTextKeyColID , @FullTextIndexActive , @FullTextCatalogName
                IF @@FETCH_STATUS < 0 BREAK
                SET @CmdStr = N'EXEC sp_fulltext_table ''' + QUOTENAME( @TableOwner ) + N'.' +
                              QUOTENAME( @TableName ) + N''' , ''drop'''
                IF @JustCheck = 1
                        PRINT @CmdStr
                ELSE
                        EXECUTE( @CmdStr )
        END
        DEALLOCATE @FullTextTables

        -- Drop fulltext catalogs
        EXEC sp_help_fulltext_catalogs_cursor @FullTextCatalogs OUTPUT
        WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
        BEGIN
                FETCH NEXT FROM @FullTextCatalogs INTO @FullTextCatID , @FullTextCatalogName , @FullTextCatPath ,
                                                       @FullTextStatus , @FullTextNumTables
                IF @@FETCH_STATUS < 0 BREAK
                SET @CmdStr = N'EXEC sp_fulltext_catalog ' + QUOTENAME( @FullTextCatalogName ) + N' , ''drop'''
                IF @JustCheck = 1
                        PRINT @CmdStr
                ELSE
                        EXECUTE( @CmdStr )
        END
        DEALLOCATE @FullTextCatalogs
        
        IF @JustCheck = 0 EXECUTE sp_fulltext_service 'clean_up'
END

-- Drop all UDFs & SPs
DECLARE drop_routine_cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT QUOTENAME( r1.ROUTINE_SCHEMA ) + '.' + QUOTENAME( r1.ROUTINE_NAME ) , r1.ROUTINE_TYPE
        FROM INFORMATION_SCHEMA.ROUTINES AS r1
        ORDER BY r1.ROUTINE_TYPE

OPEN drop_routine_cur
WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
BEGIN
        FETCH NEXT FROM drop_routine_cur INTO @RoutineName , @RoutineType
        IF @@FETCH_STATUS < 0 BREAK
        
        SET @CmdStr = N'DROP ' + @RoutineType + SPACE( 1 ) + @RoutineName
        IF @JustCheck = 1
                PRINT @CmdStr
        ELSE
                EXECUTE( @CmdStr )
END
DEALLOCATE drop_routine_cur

-- Drop all constraints, starting with FOREIGN KEY, UNIQUE & PRIMARY KEY in that order
DECLARE drop_cns_cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT QUOTENAME( c1.TABLE_SCHEMA ) + '.' + QUOTENAME( c1.TABLE_NAME ) AS TableName ,
               QUOTENAME( c1.CONSTRAINT_NAME ) As ConstraintName
          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c1
         WHERE c1.CONSTRAINT_TYPE IN ( 'PRIMARY KEY' , 'FOREIGN KEY' , 'UNIQUE' )
         ORDER BY ( CASE c1.CONSTRAINT_TYPE
                        WHEN 'FOREIGN KEY' THEN 1
                        WHEN 'UNIQUE'      THEN 2
                        WHEN 'PRIMARY KEY' THEN 3
                  END )

OPEN drop_cns_cur
WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
BEGIN
        FETCH NEXT FROM drop_cns_cur INTO @TableName , @ConstraintName
        IF @@FETCH_STATUS < 0 BREAK
        
        SET @CmdStr = N'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName
        IF @JustCheck = 1
                PRINT @CmdStr
        ELSE
                EXECUTE( @CmdStr )
END
DEALLOCATE drop_cns_cur

-- Drop all views & tables
DECLARE drop_tbl_viw_cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT i2.TABLE_NAME , i2.TABLE_TYPE
        FROM (
                SELECT QUOTENAME( i1.TABLE_SCHEMA ) + '.' + QUOTENAME( i1.TABLE_NAME ) , i1.TABLE_TYPE ,
                       OBJECT_ID( QUOTENAME( i1.TABLE_SCHEMA ) + '.' + QUOTENAME( i1.TABLE_NAME ) )
                FROM INFORMATION_SCHEMA.TABLES AS i1
        ) AS i2( TABLE_NAME , TABLE_TYPE , TABLE_ID )
        WHERE 1 IN ( OBJECTPROPERTY( i2.TABLE_ID , 'IsUserTable' ) , OBJECTPROPERTY( i2.TABLE_ID , 'IsView' ) ) And
              OBJECTPROPERTY( i2.TABLE_ID , 'IsMSShipped' ) = 0
        ORDER BY i2.TABLE_TYPE DESC

OPEN drop_tbl_viw_cur
WHILE( 'FETCH IS OK' = 'FETCH IS OK' )
BEGIN
        FETCH NEXT FROM drop_tbl_viw_cur INTO @TableName , @TableType
        IF @@FETCH_STATUS < 0 BREAK
        
        IF @TableType = 'VIEW'
                SET @CmdStr = N'DROP VIEW ' + @TableName
        ELSE IF @TableType = 'BASE TABLE'
                SET @CmdStr = N'DROP TABLE ' + @TableName

        IF @JustCheck = 1
                PRINT @CmdStr
        ELSE
                EXECUTE( @CmdStr )
END
DEALLOCATE drop_tbl_viw_cur

-- Drop all domain constraints ( rules )
DECLARE drop_dom_cns_cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT QUOTENAME( dc1.DOMAIN_NAME )
        FROM INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS AS dc1

OPEN drop_dom_cns_cur
WHILE ( 'FETCH IS OK' = 'FETCH IS OK' )
BEGIN
        FETCH NEXT FROM drop_dom_cns_cur INTO @DomainConstraintName
        IF @@FETCH_STATUS < 0 BREAK
   
        SET @CmdStr = N'EXECUTE sp_unbindrule ' + @DomainConstraintName
        IF @JustCheck = 1
                PRINT @CmdStr
        ELSE
                EXECUTE( @CmdStr )

        SET @CmdStr = N'DROP RULE ' + @DomainConstraintName
        IF @JustCheck = 1
                PRINT @CmdStr
        ELSE
                EXECUTE( @CmdStr )
END
DEALLOCATE drop_dom_cns_cur


-- Drop all domains ( UDTs )
DECLARE drop_dom_cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT QUOTENAME( dc1.DOMAIN_NAME )
        FROM INFORMATION_SCHEMA.DOMAINS AS dc1

OPEN drop_dom_cur
WHILE ( 'FETCH IS OK' = 'FETCH IS OK' )
BEGIN
        FETCH NEXT FROM drop_dom_cur INTO @DomainName
        IF @@FETCH_STATUS < 0 BREAK
   
        SET @CmdStr = N'EXEC sp_droptype ' + @DomainName
        IF @JustCheck = 1
                PRINT @CmdStr
        ELSE
                EXECUTE( @CmdStr )
END
DEALLOCATE drop_dom_cur
go 
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_drop_all_objects'), 'IsProcedure') = 1 
BEGIN 
     GRANT EXECUTE ON dbo.sp_drop_all_objects To Public 
     PRINT '<<< CREATED PROCEDURE dbo.sp_drop_all_objects >>>' 
END 
ELSE 
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_drop_all_objects >>>' 
go
-- ** END ** 
IF CHARINDEX( '8.00' , @@version ) > 0 EXEC sp_MS_upd_sysobj_category 2
go
This page was last updated on May 01, 2006 04:28 PM.