August 18, 2005 at 3:32 pm
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 19, 2005 at 2:02 am
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
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
Thx alot that did the trick
August 19, 2005 at 5:40 am
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
All the system dbs have an sid of 0x01
August 19, 2005 at 7:10 am
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
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
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
Now you'll be able to chat on SSC .
August 19, 2005 at 7:36 am
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
It doesn't work when you have your old Nick.
August 19, 2005 at 10:18 am
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy