change all synonyms to another database

Question

I have production database (for example, PROD1). It has a lot of synonyms to another database (for example, PROD2).

I have created PROD2_TEST (as fully copy of PROD2) and also PROD1_TEST (as full copy of PROD1). But as a result I have PROD1_TEST synonyms to PROD2, instead of PROD2_TEST.

How, can I automatically recreate all synonyms to the test database?

Answer 1

Based on @artm suggestion I expanded it just a little bit to include parameters to make it more generic. Here is the code

DECLARE @newDB VARCHAR(MAX) = 'newDB',  --newDB to point the synonym to
    @linkedSrv VARCHAR(MAX) = null --if the synonym points to a linked server than specify it here like '[LinkedSrv].' (including the dot in the end)

SELECT  'Drop Synonym [' + SCHEMA_NAME(schema_id) + '].[' + PARSENAME(base_object_name, 1) + '];' AS dropSynonym,
        ' CREATE SYNONYM [' + SCHEMA_NAME(schema_id) + '].[' + UPPER(PARSENAME(base_object_name, 1)) + '] FOR '
        + COALESCE(@linkedSrv, '') + '[' + @newDB + '].[' + COALESCE(PARSENAME(base_object_name, 2),
                                                                     SCHEMA_NAME(SCHEMA_ID())) + '].['
        + UPPER(PARSENAME(base_object_name, 1)) + '];' AS createSynonym
FROM    sys.synonyms`

Answer 2

I've had to do this for the same reasons and this is the query I used. Run the query, copy paste the results to the new server (PROD_Test) and run the produced queries. It handles different schema names and multiple databases in case you're using them:

SELECT 'Drop Synonym [' + SCHEMA_NAME(schema_id) + '].[' + PARSENAME(base_object_name,1) + '];CREATE SYNONYM [' + 
    SCHEMA_NAME(schema_id) + '].[' + PARSENAME(base_object_name,1) + '] FOR [' + COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) + '_Test].[' 
    + COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) + '].[' + PARSENAME(base_object_name,1) + '];'
FROM sys.synonyms