Search code examples
stored-proceduressql-server-2008-r2synonymobject-oriented-database

How to create one database object in another database's stored procedure?

scenario is, I am having 2 database. db1 and db2. where as I have around more than 45 stored procedures in db2 which are having join with tables in db1. I could able to do by db1.dbo.tablename but since if I am renaming db1 to db3. All db2 stored procedures getting failed. What should approach? as per client requirement as usual he want to rename db1. By find replace in script db1 to db3 is not the good approach. because if client asks to rename again db3 databse to some another name , then it is permanent tedious and unprofessional work. What should I use here?

Solution

  • Create synonyms. Reference the synonyms in your objects. If the database changes, you just have to change one synonym per object. There's no avoiding changing the logical name in your code but if you control access through synonyms you only have to change it in one place.

    For example

    Without synonyms:

    Six Stored Procedures in db2 refer to objects db1..Table1 and db1..Table2

    When you rename db1 to db7, you need to alter two objects in six stored procedures.

    With synonyms:

    Six Stored Procedures refer to synonyms snTable1 and snTable2 (in the local database - note no database reference here)

    The synonym snTable1 refers to db1.Table1

    When you rename db1 to db7, you need to alter two synonyms to point at the new database. No changes to stored procedures are required. All objects referring to the synonyms will still work.

    This requires you to create synonyms in db2 pointing at objects in db1, and an initial rewrite of your stored procedures in db2 to refer to local synonyms instead of database-qualified objects. But you need to do that anyway right?

    Example Procedure

    1. Create a synonym in db2 called snTable1 that refers to Table1 in db1

      USE db2

      CREATE SYNONYM snTable1 FOR db1.Table1

    2. Alter your 45 stored procedures to refer to snTable1, not db1.Table1. You only have to do this once. Note these stored procedures are referring to objects in the local database.

    3. If your database gets renamed to xyz, recreate the synonym:

      USE DB2

      DROP SYNONYM snTable1

      CREATE SYNONYM snTable1 FOR xyz.Table1

    This is only useful if there are far more stored procedures/views than objects.

    If you wish to change these on the fly you could probably use DMO or powershell or generate some T-SQL to do it. You just run the above commands against the database with a user that has suitable security.

    Another very unpleasant option that may work is to create a linked server to your local database with a hard coded login whose default database is the database that you want. But it hides the user that is really accessing the object, and probably introduces performance issues. In short it's bad practice. I question why the database needs to be renamed so much. No end user should ever see it. If you take a look at Sharepoint databases they have hideous names but this is irrelevant to an end user.

    Example linked server procedure

    1. Create a user that only has access to db1, and whose default database is db1
    2. Create a linked server (called MyLinkedServer for example) on the SQL Server to the local database using the user created in step 1
    3. Alter all your code in db2 to use four part naming through this linked server: SELECT * FROM MyLinkedServer...Table1
    4. If the database name changes, there are probably no changes required

    This is just a theory and if it works its bad practice... worse practice than needing to rename your database.