MSSQL: Better way to drop all tables?

[JoshT]

Anyone know a better way to drop all tables in a MS SQL Server 2000 Database than dropping and recreating the entire database? Especially one that won't require me to close query analyzer windows connected to that database and will take FK relationships into consideration so it doesn't throw an error instead of deleting the table.

Thanks.

USE master
GO

DROP DATABASE Purchasing

IF @@ERROR = 0 CREATE DATABASE Purchasing
GO

[vb_dba]

You could utilize the sysobjects and sysforeignkeys tables in your database to write a script that will do this for you. It will require a cursor, or two, depending on the level of foreign keys you have in your database:

CREATE PROC Drop_Tables AS

Declare @ID int
Declare @FID int
Declare @Sql varchar(500)
Declare @Table varchar(500)

Declare ID_Cursor Cursor For
    Select id
    From sysobjects
    Where ObjectProperty(id, 'IsUserTable') = 1

Open ID_Cursor

Fetch ID_Cursor Into @ID
While @@Fetch_Status = 0
Begin
     --Check to see if table is a foreign key reference
     If Exists(Select 1 From sysforeignkeys Where rkeyid = @ID)
     Begin
           Declare FID_Cursor Cursor For
                 Select fkeyid
                 From sysforeignkeys
                 Where rkeyid = @ID

           Open FID_Cursor
           Fetch FID_Cursor Into @FID
           While @@Fetch_Status = 0
           Begin
                 --Delete all tables that have foreign keys with @ID
                 Select @Table=name From sysobjects Where id = @FID
                 Print 'Dropping ' + @Table
                 Set @Sql = ''
                 Set @Sql = @Sql + 'Drop Table ' + @Table
                 Exec (@Sql)

                 Fetch FID_Cursor Into @FID
            End
            Close FID_Cursor 
            Deallocate FID_Cursor
        End

        --Drop the table
        Select @Table=name From sysobjects Where id = @ID
        Print 'Dropping ' + @Table
        Set @Sql = ''
        Set @Sql = @Sql + 'Drop Table ' + @Table
        Exec (@Sql)

        Fetch ID_Cursor Into @ID
End
Close ID_Cursor
Deallocate ID_Cursor

I didn't test this, but you should be able to use the above code to create a script to do what you want.

[JoshT]

OK, thanks, I'll try it out next time I need to rebuild my database.

[JoshT]

Ok, it seems to work well. It drops the table "dtproperties", which is what Enterprise Manager uses for the Diagrams, right?

[vb_dba]

Add an And clause to the following statement:

Declare ID_Cursor Cursor For
    Select id
    From sysobjects
    Where ObjectProperty(id, 'IsUserTable') = 1
      And name <> 'dtproperties'

This will prevent the table from being dropped. I don't know how this will affect existing diagrams you may have however.

[JoshT]

I did this, not really thinking too hard about it:

--Drop the table
	SELECT @Table = name FROM sysobjects WHERE id = @ID
	IF NOT (@Table = 'dtproperties')
		BEGIN
			PRINT 'Dropping ' + @Table
			SET @Sql = ''
			SET @Sql = @Sql + 'DROP TABLE ' + @Table
			EXEC (@Sql)
		END

Anyway, if I keep the dtproperties table the Diagram stays and works fine - dropping it cause Enterprise Manager to not be able to display the diagram.

Thanks for your help.