Scripting DROP SYNONYM

less than 1 minute read

Recently we needed to refresh our test database and the synonyms in the test environment is different from the production environment. Instead of re-creating all the synonyms one-by-one, this task can be semi-automated.

  1. Run the following code on the old production database

    SET NoCount ON -- optional
    SELECT DISTINCT 'DROP SYNONYM [' + SCH.name + '].' + SYN.name
    FROM
    Sys.Synonyms SYN
    JOIN
    Sys.Schemas SCH ON SYN.schema_id = SCH.schema_id
    WHERE
    SYN.is_ms_shipped = 0
    SET NoCount OFF -- optional
  2. Verify the synonyms to be deleted and copy the resulting DROP statements into a new query table
  3. Right-click the database and select Tasks
    tasks
  4. Select Generate Scripts
    generate_scripts
  5. Select Next
    gs_step1
  6. Select Synonyms and then Next
    gs_step2
  7. Select Save to Clipboard (or you can save to file)
    gs_step3
  8. Confirm the Server and Database. Select Next
    gs_step4
  9. Select Finish
    gs_step5
  10. Paste the create synonyms queries into the previous query window.
  11. Run on the newly restored development database

Leave a Comment

Your email address will not be published. Required fields are marked *

Loading...