Post reply

Database var

  • October 2, 2007 at 6:36 am

    #175617

    I have several client db's and many get added weekly. From a Sp I run I will get results that I will need to update in various client db's. Without having to hardcode the dbname, I would like to use

    a var for the call. I can pickup the dbname from another db. I know you can use a var for tables, but can you use to call another db?

    So that you would have select * from @db.dbo.table

  • October 2, 2007 at 7:15 am

    #738132

    Make a loop through

    SELECT [name] master.dbo.sysdatabases

  • October 2, 2007 at 7:26 am

    #738140

    You could also use dynamic SQL - i.e.

    declare @sql nvarchar(2000) , @dbname varchar(100)

    set @dbname = 'master'

    set @sql = '

    select top 100 * from ' + @dbname + '.dbo.sysobjects with(nolock)'

    EXEC sp_executesql @sql

    Tommy

    Follow @sqlscribe

  • October 2, 2007 at 8:11 am

    #738173

    You cannot have a database name for a variable as you've asked without dynamic sql.

    In SQL Server 2005, use sys.databases, not dbo.sysdatabases.

  • October 2, 2007 at 8:59 am

    #738212

    Thanks very much I should of thought of using dynamic sql for this, it worked great.

  • October 2, 2007 at 11:09 am

    #738289

    This is pretty much the best reason to use it... multiple servers/dbs/tables/columns admin tasks. While it's not good for everything, it sure has its uses in that area.

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

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