Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    17

    Unanswered: Drop all objects in database?

    I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:

    1. If I run this command

    Code:
    EXEC sp_MScheck_uid_owns_anything 5
    I get this weird error message:

    "The user owns objects in the database and cannot be dropped."

    Not sure why that is since I'm just trying to list the objects, not drop them.

    2. I tried running a simple query to get the objects from the system table instead:

    Code:
    SELECT * from [dbo].[sysobjects] where uid = 5
    This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:

    The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.

    Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!


    -Cliff

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use the TYPE column of sysobjects to filter out foreign keys, which should drop automatically when the tables are dropped.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2003
    Posts
    17
    Thanks for responding. Your approach requires that the tables be dropped in a particular order so that there won't be any constraint violations, right? I was hoping to avoid that by deleting all the foreign keys first and then dropping all the tables...

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select 'alter table ' + object_name (fkeyid) + ' drop constraint ' + object_name (constid)
    from sysreferences

  5. #5
    Join Date
    Aug 2003
    Posts
    17
    Thanks, that's getting me closer! I'll let you know how it goes

  6. #6
    Join Date
    Aug 2003
    Posts
    17
    Thanks for your help, everyone. This is what I ended up putting together:

    Code:
    ALTER PROCEDURE [rvts].[clean_database]
    as
    
    set nocount on
    
    -- first obtain all foreign keys and delete
    declare @fkTableName varchar(255)
    declare @fkConstName varchar(255)
    
    declare cursor1 cursor for
    select object_name (fkeyid), object_name (constid) from sysreferences
    
    open cursor1
    
    fetch next from cursor1
    into @fkTableName, @fkConstName
    
    while @@fetch_status = 0
    begin
      exec ('ALTER TABLE ' + @fkTableName + ' DROP CONSTRAINT ' + @fkConstName)
      fetch next from cursor1
      into @fkTableName, @fkConstName
    end
    
    close cursor1
    deallocate cursor1
    
    
    -- now do the same for tables
    declare @pkTableName varchar(255)
    
    declare cursor2 cursor for
    select object_name (id) from sysobjects where xtype = 'U'
    
    open cursor2
    
    fetch next from cursor2
    into @pkTableName
    while @@fetch_status = 0
    begin
      exec ('DROP TABLE ' + @pkTableName)
      fetch next from cursor2
      into @pkTableName
    end
    
    close cursor2
    deallocate cursor2
    I'm a T-SQL noob so if anything there looks out of place just give me a good smack

  7. #7
    Join Date
    Jun 2003
    Posts
    269

    Talking

    Quote Originally Posted by cliffyman
    Thanks for your help, everyone. This is what I ended up putting together:

    Code:
    ALTER PROCEDURE [rvts].[clean_database]
    as
     
    set nocount on
     
    -- first obtain all foreign keys and delete
    declare @fkTableName varchar(255)
    declare @fkConstName varchar(255)
     
    declare cursor1 cursor for
    select object_name (fkeyid), object_name (constid) from sysreferences
     
    open cursor1
     
    fetch next from cursor1
    into @fkTableName, @fkConstName
     
    while @@fetch_status = 0
    begin
      exec ('ALTER TABLE ' + @fkTableName + ' DROP CONSTRAINT ' + @fkConstName)
      fetch next from cursor1
      into @fkTableName, @fkConstName
    end
     
    close cursor1
    deallocate cursor1
     
     
    -- now do the same for tables
    declare @pkTableName varchar(255)
     
    declare cursor2 cursor for
    select object_name (id) from sysobjects where xtype = 'U'
     
    open cursor2
     
    fetch next from cursor2
    into @pkTableName
    while @@fetch_status = 0
    begin
      exec ('DROP TABLE ' + @pkTableName)
      fetch next from cursor2
      into @pkTableName
    end
     
    close cursor2
    deallocate cursor2
    I'm a T-SQL noob so if anything there looks out of place just give me a good smack
    hmmmmmmmm cursorssssssssss, im loving it!
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Bah! The performance problems will be lost in the wash of "Hey, where'd my table go?" complaints.

    Hopefully, you have everything owned by dbo. If not, you will have to go back, and add in the user_name(uid) function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •