Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    2

    Question Unanswered: Querying table in arbitrary database

    Would appreciate some help with this one. I need to run a query on a known table an arbitrary database. The DB name will be selected at runtime.

    What I've tried:

    DECLARE @DBName varchar(10)
    SET @DBName = 'WBTST'

    SELECT TOP 100 PERCENT Col1, Col2
    FROM [@DBName].dbo.Table1
    GROUP BY Col1

    Result:
    Invalid object name '@DBName.dbo.UPR00100'.

    I've also tried without the square brackets around @DBName in the FROM clause. This results in: Incorrect syntax near '.'.

    Thanks for the help
    Wes

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't refer directly to database object names using variable. You need to create and execute a dynamic SQL statement:
    Code:
    DECLARE @DBName varchar(10)
    DECLARE	@SQLString varchar(8000)
    SET @DBName = 'WBTST'
    
    SET @SQLString = 'SELECT TOP 100 PERCENT Col1, Col2 FROM ' + @DBName + '.dbo.Table1 GROUP BY Col1'
    exec (@SQLString)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2006
    Posts
    2

    Thanks

    Thanks. That worked.

    Wes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •