DROP all user-defined objects from a database

[Work Work Work]

I want to write a T-SQL program to identify and drop all user-defined
objects in a SQL 2000 database (tables, indexes, views, triggers, roles,
procedures, functions, etc).

Does such a system function or procedure already exist, or does someone have
one they're willing to share?

As near as I can determine this query identifies all user-defined objects:
SELECT * FROM sysobjects WHERE CATEGORY = 0

The 'xtype' column in sysobjects indicates the object type, which I can use
to iterate through the objects and drop them

Is there another reliable way to identify and drop all user-defined objects?

Thanks

[Mike John]

Why not DROP and Recreate the entire database. This will probably be faster
and a lot simpler for you.

Mike John

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

[Dan Guzman]

The script below will user drop tables, views, procedures and functions.
If you have schema-bound objects, you'll need to run this multiple times
until you get no errors.

--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" <wo...@night.com> wrote in message
news:wsun9.10055$lV3.9...@newsread1.prod.itd.earthlink.net...

[Work Work Work]

Thanks Dan.

"Dan Guzman" <dang...@nospam-earthlink.net> wrote in message
news:uT6G0TJbCHA.2528@tkmsftngp12...

[Work Work Work]

Thanks Mike. I agree, that might be the easier option.

"Mike John" <Mike...@knowledgepool.com> wrote in message
news:epZ0nEEbCHA.2324@tkmsftngp08...

[oj]

truly, if you're going to drop all user objects, you'd be better off
dropping the database and recreate it. it's going *much* faster and cleaner.

--
-oj
http://www.rac4sql.net

"Work Work Work" <wo...@night.com> wrote in message

news:W0Rn9.11150$OB5.1...@newsread2.prod.itd.earthlink.net...

[Dan Guzman]

Dropping and recreating the database is certainly cleaner (at least
compared to the script I posted) but not necessarily faster. We do
iterative testing with some larger databases (several GB) and it takes a
while to create the db. The drop script runs in a few seconds.

--
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
-----------------------

"oj" <nospam...@home.com> wrote in message
news:#i7VRSQbCHA.640@tkmsftngp11...

[John Bell]

Hi

To add to what the others have said... If this is going to be a
regular occurence then you may want to have either a standard backup
that you restore that contains the static data and table structures or
a mdf and ldf file that contain your default information which you can
attach.

John

On Sat, 05 Oct 2002 05:07:40 GMT, "Work Work Work" <wo...@night.com>
wrote:

[oj]

you're right in that if your intial catalog size is in gb. most of the
times, i want my *newly* created dbs to be as slim as pos.

--
-oj
http://www.rac4sql.net

"Dan Guzman" <dang...@nospam-earthlink.net> wrote in message

news:#P0hDcUbCHA.640@tkmsftngp11...