Converting Synonyms to Other Objects :: SQL Server 2012

You have just been assigned the database developer responsibility for a database that makes extensive use of synonyms in place of tables and views. Based on user feedback, you need to replace some of the synonyms. In the following cases, identify what actions you can take that will not cause users or applications to change their code.
  1. Some synonyms refer to tables. However, some of the tables must be filtered. You need to leave the synonym in place but somehow filter what the table returns.

  2. Some synonyms refer to tables. Sometimes column names of the table can change, but the synonym still needs to return the old column names.

  3. Some synonyms refer to views. You need to make it possible for users to see the names and data types of the columns returned by the views when the users browse the database by using SSMS.
  1. To filter the data coming from the table, you can create a view or inline function that filters the data appropriately, and recreate the synonym to reference the view or function.

  2. To keep synonyms working even if column names of a table are changed, you can create a view that refers to the tables and recreate the synonym to refer to the view.

  3. Synonyms cannot expose metadata. Therefore, when browsing a database in SSMS, users will not see column names and their data types under the synonym. In order to enable users to see the column data types of the underlying data tables, you must replace the synonym with a view.