2

I need to change the name for a few databases, however the databases are used by a lot of applications managed by several different teams. It would take some time for all the applications to finish changing connections and database name references. Is there a way to allow the change to be done seamlessly? Was looking at synonyms to temporarily allow references to the renamed database but unfortunately it is only for database level.

6
  • sorry I don't understand your question "I need to change the name for a few databases" "Was looking at synonyms to temporarily allow references to the renamed database but unfortunately it is only for database level" aren't you contradicting yourself ? Apr 22, 2021 at 9:25
  • Hi, sorry if it sounded confusing, by database level I means that it was only applicable for objects within the database and not the database itself. If it is server level, I could have a synonym to redirect old references to the renamed database temporarily.
    – JieLong
    Apr 22, 2021 at 9:52
  • 1
    can't you create a db with the name you want & then fill it with synonyms pointing to the objects you want to alias? Apr 22, 2021 at 9:58
  • that makes sense, but then I would need to create synonyms to point to every object in the renamed database
    – JieLong
    Apr 22, 2021 at 10:27
  • 1
    @JieLong, yes, you would need to create synonyms for every object. I've used that technique for use cases other than a databases rename (e.g. a synonym database used to toggle between 2 different databases).
    – Dan Guzman
    Apr 22, 2021 at 10:37

1 Answer 1

7

Create an "alias" database with the name you want to use. Create synonyms for the objects by querying the system tables to create a simple script, here for example for user tables.

select 
  'Create Synonym '
   +quotename(object_schema_name(id, db_id()))+'.'
   +quotename(o.name)+
   ' For [SourceDB].'
   +quotename(object_schema_name(id, db_id()))+'.'
   +quotename(o.name)+';' 
from sysobjects o where type = 'U'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.