How to add a Dynamic Database name to SQL Query using the column data

Mahesh Madhusanka 106 Reputation points

Hi Team,

Currently we have a requirement t get a particular table columns but that same name table available on the different table, its mean table name same and Database name Dynamic but Dynamic data table last two digit available on the previous table column (SM_DB), Currently we have a 38 databases (Dynamic Database name start from "ODYSSEYEXKPRD_SD001" to 'ODYSSEYEXKPRD_SD038' ) so we can get a last every database name last 2 digit from previous table ('StorageMain' table column name 'SM_DB' so how can we get select the relevant database name using previous table column (SM_DB) Value this Colum value parse as a 1 to 38. For your reference here with attached Sample SQL Query, Could you please check and advise on it?


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,796 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 78,026 Reputation points MVP

    Why are there 38 databases in the first place?

    One option is to set up a inline-table valued function with the database name as a parameter:

    CREATE FUNCTION OdyssesTable(@dbno int) AS RETURNS TABLE
    RETURN (
         SELECT ...
         WHERE @dbno = 1
         UNION ALL 
         SELECT ...
         WHERE @dbno = 2

    If you and a new database you will need to change the function.

    One problem is that the user running a SELECT that calls this function may not have permission to all 38 databases. I believe this works out if there already is a plan for the database in cache, but compilation will fail.

    The other alternative is dynamic SQL, which will lead to that the code is littered and more difficult to read and maintain. I have written about dynamic SQL in this article: The Curse and Blessings of Dynamic SQL.

  2. EchoLiu-MSFT 14,516 Reputation points

    The same confusion, why there are 38 or more databases to store a single table. In addition, if top is not used with order by, it is actually meaningless.