Post reply

Drop all databases but three need help with the exception

  • August 18, 2005 at 3:32 pm

    #165687

    Hi,

    I need to drop all database but three now the dropping part goes just fine with the query I wrote....it's the skipping that just won't work...

    I tried some options to skip the three databases but then this query wouldn't run and if it run it just wouldn't skip the 3 databases..

    If some1 could help me out what I should add to skip 3 databases (these are always the same) it's really appreciated.

    USE master

    GO

    DECLARE @dbname sysname

    DECLARE @sql nvarchar(1000)

    DECLARE cur CURSOR FORWARD_ONLY KEYSET FOR

    SELECT name FROM sysdatabases WHERE sid <> 0x01

    OPEN cur

    FETCH NEXT FROM cur INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = N'DROP DATABASE ' + @dbname

    exec sp_executesql @sql

    FETCH NEXT FROM cur INTO @dbname

    END

    CLOSE cur

    DEALLOCATE cur

  • August 18, 2005 at 3:46 pm

    #583355

    what do you get if you comment out --exec sp_executesql @sql and

    print @sql ?

  • August 19, 2005 at 2:02 am

    #583424

    Well if I do that I get a list of all the database names that will be dropped(DROP DATABASE X and so on). But I don't see how that helps me?

    There are three databases that the program I use needs to function, so I can't drop them but I need the drop all the others I worked on after a backup is made end of the day. Now if I had to do that one by one that will take me a very long time.....

    Still going through BOL but ..... I haven't a clue yet how to skip those three....

    Maybe I just need a break.... still any help is welcome

  • August 19, 2005 at 2:45 am

    #583440

    Change:

    SELECT name FROM sysdatabases WHERE sid <> 0x01

    To

    SELECT name FROM sysdatabases WHERE sid <> 0x01

         AND name NOT IN ('master','model','msdb')

    This way they are excluded from your loop.

    Andy

  • August 19, 2005 at 5:28 am

    #583471

    Thx alot that did the trick

  • August 19, 2005 at 5:40 am

    #583473

    what about tempdb ?!

    Andy - I still don't understand how explicitly eliminating the system dbs works - doesn't WHERE sid 0x01 do just that - not include the system dbs....?!?! could you please explain ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 19, 2005 at 6:59 am

    #583492

    All the system dbs have an sid of 0x01

  • August 19, 2005 at 7:10 am

    #583498

    that's what my question is about remi - when all the system dbs have a sid of 0x01.. why did andy change the query to....

    SELECT name FROM sysdatabases WHERE sid 0x01

    .....

    AND name NOT IN ('master','model','msdb')...

    and even more importantly - why did it work only when explicitly included by name ?!?!?!?!?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 19, 2005 at 7:21 am

    #583504

    maybe he should have written :

    SELECT name FROM sysdatabases WHERE sid 0x01

    AND name NOT IN ('keepdb1','keepdb2','keepdb3')

    it would have been less confusing.

  • August 19, 2005 at 7:25 am

    #583506

    now that makes perfect sense...thx. for the interpretation - would've been puzzling over this all day long otherwise...







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 19, 2005 at 7:33 am

    #583513

    Now you'll be able to chat on SSC .

  • August 19, 2005 at 7:36 am

    #583514

    CHAT........I never chat....you must have me confused with someone else!!!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 19, 2005 at 7:42 am

    #583518

    It doesn't work when you have your old Nick.

  • August 19, 2005 at 10:18 am

    #583615

    Yes but I thought that the sid <> 0x01 would make it clear that

    AND name NOT IN ('msdb','master','model') would be read as

    AND name NOT IN ('keepdb1','keepdb2','keepdb3')

    Ofcourse I didn't realize not every1 knows what sid <> 0x01 means....

    I already was happy that some1 helped me out with the keepdb part I guess I am just better in writing the Delete part

    I hope some1 else has some use for this query at least it is clear now what it does.... I

     

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply