SQL Server cross database alias

问题:

I'm trying to understand how I can use an alias to reference another database in the same instance, without having to use a hardcoded name.

The scenario is as below:

I have a data db with stores data, an audit db which keeps all changes made. for various reason, i want to keep the audit data in a separate database, not least because it can get quite large and for reporting purposes.

In the data db, I don't want to reference this by a hardcoded name but an alias so that in different environments, I don't have to change the name and various sp's to reference the new name.

for example:

mydevdata
mydevaudit

If a sp exists in mydevdata such as which calls the mydevaudit, I don't want to change the sp when I go to test where the db's may be called mytestdata and mytestaudit. Again, for various reasons, the database names can change, more to do with spaces an instances etc.

So if I had procedure in mydevdata:

proc A
begin

insert into mydevaudit.table.abc(somecol)
select 1

end

when I go to test, I don't want to be change the procedure to reference another name, (assume for sake of argument that happened)

Instead I am looking to do something like:

proc A
begin

insert into AUDITEBALIAS.table.abc(somecol)
select 1

end

I am interested in finding out how I could do something like that, and the pro's and cons.

Also, dymnamic SQL is not an option.

thanks in advance for you help.

回答1:

You may be able to use synonyms

CREATE SYNONYM WholeTableAliasWithDBetc FOR TheDB.dbo.TheTable

This means all object references in the local DB are local to that DB, except for synonyms that hide the other database from you.

You can also use stored procedures in the audit DB. There is a 3rd form of EXEC that is little used where you can parametrise the stored proc name

DECLARE @module_name_var varchar(100)
SET @module_name_var = 'mydevaudit.dbo.AuditProc'
--   SET @module_name_var = 'whatever.dbo.AuditProc'
EXEC @module_name_var @p1, @p2, ...

Obviously you can change module_name_var to use whatever DB you like

回答2:

I've just posted this to How to create Sql Synonym or "Alias" for Database Name? which is a workaround for the same situation:

There is a way to simulate this using a linked server. This assumes you have two SQL servers with the same set of databases one for development/test and one live.

1. Open SQL Server Management Studio on your development/test server
2. Right click Server Objects > Linked Servers
3. Select New Linked Server...
4. Select the General page
5. Specify alias name in Linked server field - this would normally be the name of your live server
6. Select SQL Native Client as the provider
7. Enter sql_server for Product Name
8. In Data Source specify the name of the development server
9. Add Security and Server Options to taste
10. Click OK

The above is for SQL Server 2005 but should be similar for 2008

Once you've done that you can write SQL like this:

SELECT * FROM liveservername.databasename.dbo.tablename

Now when your scripts are run on the development server with the linked server back to itself they will work correctly pulling data from the development server and when the exact same scripts are run on the live server they will work normally.