--drop all user objects

DECLARE @DropStatement nvarchar(1000)
DECLARE @LastError int
DECLARE @TablesDropped int

IF DB_NAME() IN ('master', 'msdb', 'model')
BEGIN
 PRINT 'Not for use on system databases'
 GOTO Done
END

--drop views
DECLARE DropStatements
 CURSOR LOCAL FAST_FORWARD FOR
SELECT
 N'DROP ' +
  TABLE_TYPE +
  N' ' +
  QUOTENAME(TABLE_SCHEMA) +
  N'.' +
  QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
 TABLE_TYPE = 'VIEW' AND
 OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
  N'.' +
  QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN DropStatements
WHILE 1 = 1
BEGIN
 FETCH NEXT FROM DropStatements INTO @DropStatement
 IF @@FETCH_STATUS = -1 BREAK
 RAISERROR (@DropStatement, 0, 1) WITH NOWAIT
 EXECUTE sp_ExecuteSQL @DropStatement
 SET @LastError = @@ERROR
 IF @LastError <> 0 AND @LastError <> 3729
 BEGIN
  CLOSE DropStatements
  DEALLOCATE DropStatements
  GOTO Done
 END
END
CLOSE DropStatements
DEALLOCATE DropStatements

--drop procedures and functions
DECLARE DropStatements
 CURSOR LOCAL FAST_FORWARD FOR
SELECT
 N'DROP ' +
  ROUTINE_TYPE +
  N' ' +
  QUOTENAME(ROUTINE_SCHEMA) +
  N'.' +
  QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
 OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
  N'.' +
  QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
OPEN DropStatements
WHILE 1 = 1
BEGIN
 FETCH NEXT FROM DropStatements INTO @DropStatement
 IF @@FETCH_STATUS = -1 BREAK
 RAISERROR (@DropStatement, 0, 1) WITH NOWAIT
 EXECUTE sp_ExecuteSQL @DropStatement
 SET @LastError = @@ERROR
 IF @LastError <> 0
 BEGIN
  CLOSE DropStatements
  DEALLOCATE DropStatements
  GOTO Done
 END
END
CLOSE DropStatements
DEALLOCATE DropStatements

--drop foreign keys
DECLARE DropStatements
 CURSOR LOCAL FAST_FORWARD FOR
SELECT
 N'ALTER TABLE ' +
  QUOTENAME(TABLE_SCHEMA) +
  N'.' +
  QUOTENAME(TABLE_NAME) +
  N' DROP CONSTRAINT ' +
  QUOTENAME(CONSTRAINT_NAME)
FROM
 INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
 CONSTRAINT_TYPE = 'FOREIGN KEY' AND
 OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
  N'.' +
  QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN DropStatements
WHILE 1 = 1
BEGIN
 FETCH NEXT FROM DropStatements INTO @DropStatement
 IF @@FETCH_STATUS = -1 BREAK
 RAISERROR (@DropStatement, 0, 1) WITH NOWAIT
 EXECUTE sp_ExecuteSQL @DropStatement
 SET @LastError = @@ERROR
 IF @LastError <> 0
 BEGIN
  CLOSE DropStatements
  DEALLOCATE DropStatements
  GOTO Done
 END
END
CLOSE DropStatements
DEALLOCATE DropStatements

--drop tables
DECLARE DropStatements
 CURSOR LOCAL FAST_FORWARD FOR
SELECT
 N'DROP TABLE ' +
  QUOTENAME(TABLE_SCHEMA) +
  N'.' +
  QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
 TABLE_TYPE = 'BASE TABLE' AND
 OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
  N'.' +
  QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN DropStatements
WHILE 1 = 1
BEGIN
 FETCH NEXT FROM DropStatements INTO @DropStatement
 IF @@FETCH_STATUS = -1 BREAK
 RAISERROR (@DropStatement, 0, 1) WITH NOWAIT
 EXECUTE sp_ExecuteSQL @DropStatement
 SET @LastError = @@ERROR
 IF @LastError > 0
 BEGIN
  CLOSE DropStatements
  DEALLOCATE DropStatements
  GOTO Done
 END
END
CLOSE DropStatements
DEALLOCATE DropStatements

Done:
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Work Work Work" <w...@night.com> wrote in message
news:wsun9.10055$lV3.930364@newsread1.prod.itd.earthlink.net...