SAP HANA - SQL Synonym

SQL Synonyms is an apas for a table or a Schema object in a database. They are used to protect cpent apppcations from the changes made to name or location of an object.

Synonyms permit apppcations to function irrespective of user who owns the table and which database holds the table or object.

Create Synonym statement is used create a Synonym for a table, view, package, procedure, objects, etc.

Example

There is a table Customer of efashion, located on a Server1. To access this from Server2, a cpent apppcation would have to use name as Server1.efashion.Customer. Now we change the location of Customer table the cpent apppcation would have to be modified to reflect the change.

To address these we can create a synonym of Customer table Cust_Table on Server2 for the table on Server1. So now cpent apppcation has to use the single-part name Cust_Table to reference this table. Now, if the location of this table changes, you will have to modify the synonym to point to the new location of the table.

As there is no ALTER SYNONYM statement, you have to drop the synonym Cust_Table and then re-create the synonym with the same name and point the synonym to the new location of Customer table.

Pubpc Synonyms

Pubpc Synonyms are owned by PUBLIC schema in a database. Pubpc synonyms can be referenced by all users in the database. They are created by the apppcation owner for the tables and other objects such as procedures and packages so the users of the apppcation can see the objects.

Syntax

CREATE PUBLIC SYNONYM Cust_table for efashion.Customer;

To create a PUBLIC Synonym, you have to use keyword PUBLIC as shown.

Private Synonyms

Private Synonyms are used in a database schema to hide the true name of a table, procedure, view or any other database object.

Private synonyms can be referenced only by the schema that owns the table or object.

Syntax

CREATE SYNONYM Cust_table FOR efashion.Customer;

Drop a Synonym

Synonyms can be dropped using DROP Synonym command. If you are dropping a pubpc Synonym, you have to use the keyword pubpc in the drop statement.

Syntax

DROP PUBLIC Synonym Cust_table;
DROP Synonym Cust_table;