SQL Server Cross Database Attribute

Question

I am trying to understand how I can use an alias to refer to another database in the same instance, without the need for a hard-coded name.

The scenario is as follows:

I have db data with store data, db audit, which saves all changes made. for various reasons, I want to save audit data in a separate database, not least because it can become quite large for reporting purposes.

In db data, I don’t want to refer to it by a hard-coded name, but an alias, so in different environments I do not need to change the name and different sps to refer to the new name.

eg:

mydevdata mydevaudit 

If there is an sp in mydevdata that calls mydevaudit , I don't want to change the sp when I go to check where db can be called mytestdata and mytestaudit . Again, for various reasons, database names can change more than spaces, instances, etc.

So, if I had a procedure in mydevdata :

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

when I go to the test, I don’t want to change the procedure to refer to another name (suppose for the argument that happened)

Instead, I want to do something like:

 proc A begin insert into AUDITEBALIAS.table.abc(somecol) select 1 end 

I am interested to know how I can do something like this, both pros and cons.

In addition, dymnamic SQL is not an option.

in advance for help.

Answer 1

You can use synonyms

 CREATE SYNONYM WholeTableAliasWithDBetc FOR TheDB.dbo.TheTable 

This means that all references to objects in the local database are local to this database, with the exception of synonyms that hide another database from you.

You can also use stored procedures in the audit database. There is a 3rd form of EXEC , which is not used much where you can parameterize the stored name proc

 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 any DB available to you

Answer 2

I just posted this in How to create Sql or "Alias" syntax for a database name? which is a workaround for the same situation:

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

• Open SQL Server Management Studio on the development / testing server
• Right-click Server Objects> Linked Servers
• Select New Linked Server ...
• Choose a shared page
• Enter the name alias in the Linked server field - usually this is the name of your live server
• Choose your own SQL client as the provider
• Enter sql_server for the product name
• In the data source, specify the name of the development server
• Add security and server options to taste
• Click OK

The above applies to SQL Server 2005, but should be similar to 2008

Once you have done this, you can write SQL as follows:

 SELECT * FROM liveservername.databasename.dbo.tablename 

Now, when your scripts are launched on the development server with the server attached to them, they will correctly extract data from the development server and when they run the same scripts on the real server, they will work fine.