Database Aliases

[RichS]

Is it possible to create an Alias for a database, much like you can alias servers or tables.

We change our database names according to their current functions such as DB1_UAT for Testing and DB1_Prod for production. We would like for the database references in all our cross-database stored procedures to remain the same regardless of what database they are in.

Any thoughts?

Thanks,
Rich

 

[TJRTech]

Nope you can't do that. It would be extremely powerful, but you can't do it.

TR

 

[Krickles]

I agree with TJRTech's answer. However, there are work arounds depending on how you write your stored procedures and in what way you are using the db reference. If you are writing your queries using databasename.dbo.table, then you can't use an alias or variable. If you need to identify the result sets by database (e.g., in union result sets) then there is more than one way to achieve this.

Krickles | 1.6180

 

[ESquared]

You can add a linked server, which could be a method of aliasing. You'd need to investigate to see if there are any performance issues.

 

[RichS]

Thanks very much for the replies. Guess you can't get there from here. I was afraid of that. I suppose what we'll do is just script the database, do a find and replace on the database name and run the script. There will still be the problems with maintenance as we move maintenance solutions from Dev to Prod.

It would be a slick little project to write a tool that automate this.

Thanks again.

 

[ESquared]

Did you discard the linked server idea?

 

[TJRTech]

ESquared,

I considered that as an idea but it seemed to me that it wasn't really what was needed.

It sounded like the databases were local, and that there was the need to be able to switch between identical local databases easily (often the case for versioning, development and tested databases, etc).

The linked server simply makes for a potentially constant SERVER name, not a consistent database name; or am I missing something?

TR

 

[ESquared]

You weren't missing something, I was.

 

[Krickles]

TJRTech & ESquared:

I was talking to someone else about dynamically naming the database and he suggested trying something like

DECLARE @dbn varchar(50), @dbname varchar(200)
SELECT @dbn = (SELECT db_name())
SELECT @dbname = ('SELECT [fieldname] FROM ' + @dbn + '.dbo.[tablename]')
EXEC @dbname

I can't get this to work because of the first period in the string '.dbo.[tablename]'. I'm hoping that I've been trying to hard and have made a syntax error.

I might be going down the wrong path for this thread, but this solution will add the ability to dynamically name dbs based on the location of the sproc (if it works!).

Maybe you have used a similar method or can get this to work? Judging by your posts, I'm hoping your expertise will figure out a way.

Krickles | 1.6180

 

[TJRTech]

You can always use dynamic sql (e.g. executing the SQL command that is in a string) via the EXEC command or the xp_SQLExecute extended stored proc.

However, I didn't recommend this because of the overhead involved. The nice things about stored proc is they get compiled and the overhead of executing them is minimal; BUT if you use dynamic sql, then the compilation is moot and things start to slow down due to the extra work the query processor must do.


There is nothing interesting about dynamic sql other than building the string and executing it, so if your string works in query analyser, it will work when executed via EXEC.

TR

 

[SQLSister]

Dynamic SQL is a bad idea for this. Personally I would have each environment on a separate server and give each the same name. Thats what we do.

Questions about posting. See faq183-874