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.