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

Question

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?

Answer 1

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 ...
     FROM   ODYSSEYEXKPRD_SD001
     WHERE @dbno = 1
     UNION ALL 
     SELECT ...
     FROM   ODYSSEYEXKPRD_SD002
     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.

Answer 2

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.