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.
- 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.
- Some synonyms refer to tables. Sometimes column names of the table can change, but
the synonym still needs to return the old column names.
- 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.
Answers
- 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.
- 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.
- 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.