Post reply

use when database is a variable

  • August 26, 2004 at 1:30 am

    #181847

    Is it possible to change the current database in a script when the database is a parameter ie

    use @input_dbname

    go

    The above gives a syntax error (it expects and identifier), so is there another way to do it?

    The specific problem I am trying to solve is use sp_helpuser in a script that inputs the database name as a parameter.

    Thanks in advance.

  • August 26, 2004 at 2:11 am

    #520645

    Hi Frank, check out this thread for more info on this.

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=127527

    Regards

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 26, 2004 at 3:22 am

    #520660

    Not knowing the full extent of your scripts, but this one-liner will show you the name of each database and execute sp_helpuser within the context of each db on the server...

    exec sp_msforeachdb 'use ? select db_name() exec sp_helpuser'

    Perhaps something you could use?

    (note: sp_msforeachdb is undocumented, so the usual "is-not-supported-may-change" cautions applies)

    However, it's been around for quite a while, and may make things easier.

     

    /Kenneth

  • August 27, 2004 at 2:32 am

    #520823

    Thanks. Those ideas and articles made me think about what is going on. It looks like they disallow the changing of the database context using a variable due to the design of the parser or the execution plan compilation.

    This works for commands that are dependent on the current database:

    declare @dbname varchar(10)

    declare @stmt nvarchar (100)

    set @dbname = 'msdb'

    set @stmt = 'use ' + @dbname + ' ' + 'exec sp_helpuser'

    exec sp_executesql @stmt

    Cheers,

    Frank

Viewing 4 posts - 1 through 3 (of 3 total)

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