Drop all databases but three need help with the exception
-
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
-
what do you get if you comment out --exec sp_executesql @SQL and
print @SQL ?
-
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
-
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
-
Thx alot that did the trick
-
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 !!!** -
All the system dbs have an sid of 0x01
-
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 !!!** -
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.
-
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 !!!** -
Now you'll be able to chat on SSC
. -
CHAT........I never chat....you must have me confused with someone else!!!
**ASCII stupid question, get a stupid ANSI !!!** -
It doesn't work when you have your old Nick.
-
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