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.