sp_dboption

Question

sp_dboption

Freddy Freeloader

SSC Eights!

Points: 922
More actions
December 18, 2003 at 8:50 am

#107033

Hi all,
A newbie question for you all.
I'm trying to use sp_dboption to make the same change on multiple user databases at the same time. I think I can do this using nested sprocs but it's pretty complicated for a newbie.
I figured I'd create sproc declare the dboption variables and then set those variables. I can set the @optname and @optvalue very easily, but I'm having trouble figuring out how pass multiple database names into sp_dboption. My thought was to use a select query from the master database name column where dbid > 4 to make sure I don't affect any system databases, but NAME is a reserved word.
Am I going at this in way to complicated a manner? Am I all wet in my approach? If I'm not, or even if I am, just how would I accomplish this?
Any and all help would be appreciated. My main goal in this exercise is to see if I'm thinking through the problem correctly as t-sql is my first coding experience and I'm teaching myself, but I'd also like to solve the problem too.
TIA
Gary

Viewing 7 posts - 1 through 6 (of 6 total)

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

jxflagg Hall of Fame Points: 3817 More actions · Answer 1

quote:
My thought was to use a select query from the master database name column where dbid > 4 to make sure I don't affect any system databases, but NAME is a reserved word.

I presume you mean that you're using a cursor. You should be able to select name from master.dbo.sysdatabases even with name being a reserved words. I do it myself. But if you do run into a conflict with any reserved words, you can simply enclose the word in square brackets, like this:

Select [name]

from master.dbo.sysdatabases

Freddy Freeloader SSC Eights! Points: 922 More actions · Answer 2

jxflagg,

I just had to drive my wife to work and on my way there I realized that a cursor would be the tool to use. Thanks for confirming this. I appreciate it.

Freddy Freeloader SSC Eights! Points: 922 More actions · Answer 3

Hi once again.

I've put something together but I'm misunderstanding a part of the process of taking the cursor output and placing it into sp_dboption. I can create the sproc and the cursor but when I run it I get an error stating that a cursor name "next" doesn't exist.

Could I get you to take a look at my code and tell me how I'm screwing this up. Once you get up off the floor from laughing at my newbie mistakes I'd really appreciate some more help. 🙂

ALTER proc sptakedbsoffline

as

Declare @dbname varchar(20)

Declare @optname varchar(10)

Declare @optvalue varchar(6)

set @optname = 'offline'

set @optvalue = 'true'

declare DbName Cursor

Global

for

select [name] from master.dbo.sysdatabases where dbid > 4

Open DbName

Fetch next from DbName into @dbname --populate variable from cursor

While @@Fetch_status=0

Begin

exec sp_dboption @dbname, @optname, @optvalue

Fetch next into @dbname

end

close dbname

deallocate dbname

Freddy Freeloader SSC Eights! Points: 922 More actions · Answer 4

I just figured it out. I'd given it up for a while and had an inspiration and it worked.

jdausubel SSC Veteran Points: 281 More actions · Answer 5

EXEC sp_MSForeachdb 'if "?" IN ("db1", "db2", "db2") EXEC sp_dboption ?, "dbo only", true'

Edited by - jdausubel on 12/22/2003 10:51:35 AM

noeld SSC Guru Points: 96590 More actions · Answer 6

quote:
EXEC sp_MSForeachdb 'if "?" IN ("db1", "db2", "db2") EXEC sp_dboption ?, "dbo only", true'
Edited by - jdausubel on 12/22/2003 10:51:35 AM

OR:



EXEC sp_MSForeachdb 'if db_id(''?'') > 4 EXEC sp_dboption ?, ''dbo only'', true'

* Noel