SQL Server cross database alias -

i'm trying understand how can use alias reference database in same instance, without having use hardcoded name.

the scenario below:

i have data db stores data, audit db keeps changes made. various reason, want keep audit data in separate database, not least because can quite large , reporting purposes.

in data db, don't want reference hardcoded name alias in different environments, don't have change name , various sp's reference new name.

for example:

mydevdata mydevaudit 

if sp exists in mydevdata such calls mydevaudit, don't want change sp when go test db's may called mytestdata , mytestaudit. again, various reasons, database names can change, more spaces instances etc.

so if had procedure in mydevdata:

proc begin  insert mydevaudit.table.abc(somecol) select 1  end 

when go test, don't want change procedure reference name, (assume sake of argument happened)

instead looking like:

proc begin  insert auditebalias.table.abc(somecol) select 1  end 

i interested in finding out how that, , pro's , cons.

also, dymnamic sql not option.

thanks in advance help.

you may able use synonyms

create synonym wholetablealiaswithdbetc thedb.dbo.thetable 

this means object references in local db local db, except synonyms hide other database you.

you can use stored procedures in audit db. there 3rd form of exec little used can parametrise 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 can change module_name_var use whatever db like