Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: The "master" database file

    In the object explorer of SQL Server Management Studio, there is a folder called "System Databases" and underneath it are the 4 standard databases created for each database server: master, model, msdb, and tempdb.

    I've accidently added an entire schema of tables (approx 30 tables) to the "master" database? Is there any quick way for me to remove these all? Or is there even a "reset" mechenism to reset all of these back to their oriignal states?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    drop table YourTableNameHere
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by Thrasymachus
    drop table YourTableNameHere
    Well yeah, I would normally use that, but I have a bunch of foreign key dependencies. Is there an easier way rather than having to find the dependencies and drop them in that order?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are you looking to move these tables, or just drop them? If you are just dropping them, I would be inclined to generate all 30 DROP TABLE statements, and run that whole script three or four times in the master database. Provided, of course, that none of your tables' names begin with "sys". Yes, you will get some foreign key constraint errors at first, but after the fourth run, it should all be "table not found" errors.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbguyfh
    Is there an easier way rather than having to find the dependencies and drop them in that order?
    you don't know the dependencies? you have to look for them? didn't you design those tables?

    another easy way it to uninstall and reinstall

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Go with McCrowley's solutions, and keep running the script until all the tables are gone. Errors, be damned.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Somewhere there's a function or stored proc that comes with SQL Server 2005 that allows you to list tables in the order of their underlying dependencies.

    Now you're going to make me go look it up!

    (While I wait for my d@mn Oracle database to finish recovering.)

    Regards,

    hmscott
    Have you hugged your backup today?

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245

    Thumbs up

    meh...found it

    sp_MSdependencies

    combine this with a little:
    Code:
    SELECT 
    'DROP TABLE [' + user_name(uid) + '].[' + name + '];'
    FROM
      sys.sysobjects
    where
      OBJECTPROPERTY(id, 'IsMSShipped') = 0 and xtype = 'U'
    
    union 
    
    'DROP PROC [' + user_name(uid) + '].[' + name + '];'
    FROM
      sys.sysobjects
    where
      OBJECTPROPERTY(id, 'IsMSShipped') = 0 and xtype = 'P'
    
    UNION
    ...
    [well, you get the idea]
    ...
    and you should be in business.

    YMMV. Be REALLY careful when you drop objects from master. I know someone who did that once.



    Regards,

    hmscott

    PS. But that was a really long time ago.
    Have you hugged your backup today?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by hmscott
    Be REALLY careful when you drop objects from master. I know someone who did that once.
    I'm on the edge of my seat - what happened to this person who dropped objects from master?!
    George
    Home | Blog

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by georgev
    I'm on the edge of my seat - what happened to this person who dropped objects from master?!
    I'm...er...he's not saying anything!



    Actually, I'm trying to remember now; it was a while back -- more than 4 years ago. It was a "legacy" (built before I started) server. A developer had dropped a ton of objects into master by mistake and I was trying to clean it out (it was a development database, not prod). I think I was able to import a copy of that table from another database and everything was gtg.


    Regards,

    hmscott
    Have you hugged your backup today?

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Run the script in QA and copy the result. Then run the result.

    Code:
    select cmd from (
       select distinct tblID = o.id, 
          tblName = '[' + rtrim(user_name(o.uid)) + '].[' + rtrim(object_name(o.id)) + ']',
          fkID = fk.constid, FK_Name = '[' + rtrim(object_name(fk.constid)) + ']', cmd =
          'alter table [' + rtrim(user_name(o.uid)) + '].[' + 
          rtrim(object_name(fk.fkeyid)) + '] drop constraint [' +
          rtrim(object_name(fk.constid)) + ']' 
          from sysforeignkeys fk
          inner join sysobjects o on fk.fkeyid = o.id
    ) x
    union all
    select 'drop table [' + rtrim(user_name(o.uid)) + '].[' + 
          rtrim(object_name(o.id)) + ']'
          from sysobjects o
          where objectproperty(o.id, 'ismsshipped') = 0
          and objectproperty(o.id, 'isusertable') = 1
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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