Create Public Synonym in SAP HANA Database for Tables

On my SAP HANA Cloud trial account, I created a database including some demo tables.
For example, a sample HANA database table including employee data is named sap.hana.democontent.epm.data::MD.Employees :)
Additionally, this table name is not enough, we have the schema name as well SAP_HANA_DEMO
In short, SAP HANA SQLScript developers should deal with table names whose names are very long. The fully qualified name for employees table is "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Employees"

Here is a simple SQL Select statement


select top 3 employeeid, "NAME.FIRST", "NAME.LAST"

from "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Employees";

Code

I'm sure none of the SQL developers or ABAP programmers will be comfortable with such long HANA database object names.

At this point, SQL Synonym objects help developers. SQL synonyms represent the name of the HANA database object in a short form which can be thought as an alias for the database table or the schema object in that HANA database.

Create Synonym for HANA Database Table

Let's make an example. I want to alias the database table sap.hana.democontent.epm.data::MD.Employees as Employees to make coding easier with less code errors.

Without the creation of SQL synonym for the target database table, the following SELECT statement will cause below error:

Semantic Error: Unresolved table or view EMPLOYEES.

First we should create the table synonym using CREATE SYNONYM command on HANA database


create synonym Employees for "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Employees";

Code

After the execution of the above CREATE SYNONYM SQL statement, database developer will be able to see the synonym object under the Synonyms node of the table's schema.

SAP HANA database synonyms for sql objects

Double click to open definition of the target HANA database SQL synonym object

HANA database synonym definition for table object

It is possible to execute following SQL Select statement without any error now


SELECT TOP 3

employeeid, "NAME.FIRST", "NAME.LAST"

FROM Employees;

-- or adding the schema object name

select * from "SAP_HANA_DEMO".Employees

Code

Create Public Synonym for HANA Database Objects

Public mark is an important property of synonyms. Public synonyms are accessible by any user. So using a public synonym a database user can read data from another schema table.


create public synonym Employees

for "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Employees";

Code

This time SQL programmer will find the recently created public synonym in Catalog > Public Synonyms folder in SAP HANA database.

SAP HANA database public synonyms

Delete Synonym Object using SQL DROP SYNONYM Command

SQL developers can delete the synonym using DROP SYNONYM command as follows:


drop synonym Employees;

Code