Determine a database name on the fly?

Question

Determine a database name on the fly?

jchapman

SSC Eights!

Points: 989
More actions
April 8, 2008 at 4:26 pm

#185122

Here's a good one...and I'm completely stumpped.
I'm working in a SQL 2005 environment where one of the potential datasources I need to query is populated from a system that creates a new database each month. I need to be able to tie this information back into data I'm keeping in a warehouse. Based on my run date, I can mathmatically predict the name of the most recent database (or the name of the database I need based on the period I'm reporting on).
the million dollar question is, is there a way to programattically alias a database (even if I need to run something on a schedule) that will remove the need for me to modify my query each month?
for example here's what I'm working with now:
Select stuff, more_stuff, and_lots_more_stuff
from warehouse.Customers cust
inner join linked_server_name.DBName2008031.Households hh
on cust.individual_id = hh.individual_id
What I want to do is to be able to find a way to alias the name of that database (or dynamically create the database name on the fly) so that I can create a standard query.
Any ideas? Thanks in advance.

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

What I have done in the past with similar situations is to right my working queries against a set of local Views with the same names as the tables in the monthly DB's that I actually wanted to use.

Then I wrote scripts that automatically ALTERed the views to point to most recent month's DB and wrote a Job that executed this script at the beginning of each month.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

N J Right there with Babe Points: 732 More actions · Answer 2

If you are ready to do it dynamically..this would work.

declare @dbname sysname

select @dbname = name from [linked_server_name].master.dbo.sysdatabases

where crdate in (select max(crdate) from [linked_server_name].master.dbo.sysdatabases

where name not in ('master','msdb','tempdb'))

EXEC('Select cust.column_name, cust.column_name

from warehouse.Customers cust

inner join [linked_server_name].'+ @dbname+'.dbo.households hh

on cust.individual_id = hh.individual_id')

NJ

ksullivan SSCrazy Points: 2346 More actions · Answer 3

What about using synonyms? One downside is that you would have to drop and create them every month much like the view definitions.

create table TestIt (a int, b int)

create table TestIt2 (a int, b int)

insert into TestIt select 1,2

insert into TestIt2 select 300,400

CREATE SYNONYM MyTest

FOR TestIt;

GO

select * from MyTest

DROP SYNONYM MyTest

CREATE SYNONYM MyTest

FOR TestIt2;

GO

select * from MyTest

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 4

the problem with synonyms is they're global. so if you're logged into 2 (or more sessions) and redefine a synonym on connection A, connection B's synonym is changed also. does anyone know how to create a session only schema (such as create schema #myschema)? i think this would be a great alternative to awkward dynamic SQL... just decare a synonym (dynamically) and then write 'normal' SQL against the synonymed object without worring about quotes and casting and whatnot.

back to the original poster's question, we have 40 'local' databases that have identical schemas (tables, views, etc.). the way we handle it is we use the database of choice and define synonyms to all of the common objects (which exist in 4 other databases). so no matter what db you USE, you appear to have all tables/views/etc defined in it.

here's a rudimentary stored proc to create synonyms for specified objects in the another databases. you'll have to enhance it to meet your needs.

ALTER procedure [dbo].[uspPointToDB]( @dbname varchar(32), @schema varchar(32) = '', @type varchar(32) = 'U,V' )

as

begin

select name, type into #SO from sysobjects where 1 = 2

declare @prefix varchar(66)

set @prefix = @dbname + '.'+ @schema + '.'

declare @sql varchar(max)

set @sql = 'insert into #SO( name, type )

select name, type from '+ @prefix + 'sysobjects where charindex('',''+rtrim(type)+'','','','+ @type +','') > 0'

print @sql

execute (@sql)-- load #SO with approriate items

set @sql = ''

select @sql = @sql + '

if (object_id(''dbo.'+ name + ''') is not null) drop synonym dbo.'+ name from #SO

print @sql

execute (@sql)-- drop any existing synonyms

set @sql = ''

select @sql = @sql + '

create synonym '+ name + ' for '+ @prefix + name from #SO

print @sql

execute (@sql)

end

BTW, a stored proc appears to always be bound to the objects in it's database. so, if dbA.dbo.uspDoThis implicitly references table dbA..MyTable, a synonym created for it in dbB will still reference dbA..MyTable. Thus a synonymed proc probably won't work as anticipated.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Why can't you just create a linked server to that server and query the Master database on it for all the database names? That way you don't have to predict a name either manually or by formula.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jchapman SSC Eights! Points: 989 More actions · Answer 6

SYNONYMS! That is EXACTLY what I was looking for... I can create standard synonyms that are rebuilt at the first of the month via a scheduled store procedure...and I can create views that derive from standard synonyms and then modify them on the fly inside stored procedures if I need to reach back in time.

THANKS!