Consultor Eletrônico

Status: Unverified

GOAL:

SQL: How to create public SQL SYNONYMs for all the user tables of the connected database?

GOAL:

How to generate SQL script to create public SQL SYNONYMs for all the user tables of the connected database using 4GL/ABL?

GOAL:

How to generate WinSQL compatible SQL script to create public SQL SYNONYMs for all the user tables of the connected database using 4GL/ABL and GRANT SELECT privileges on these SYNONYMs to PUBLIC?

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.x
OpenEdge Category: Language (4GL/ABL)
OpenEdge Category: SQL

FIX:

The following 4GL/ABL procedure generates an SQL script to create PUBLIC SQL SYNONYMs for all the user tables of the connected database and GRANTs SELECT privileges on these SYNONYMs to PUBLIC:

/***************GenerateSynonymsForAllUserTables_WinSQL.p************/
DEFINE VARIABLE cSQLSynonymOwnerName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSQLSynonymName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
ASSIGN
cSQLSynonymOwnerName = "yshanshi".
/********Output to the constructed SQL script file name********/
OUTPUT TO VALUE("CreatePublicSynonymsForAllUserTables_WinSQL" + ".sql").
/********************Set the schema owner name*****************/
PUT UNFORMATTED "SET SCHEMA '" cSQLSynonymOwnerName "'" SKIP.
PUT UNFORMATTED "GO" SKIP.
/**Drop then create a public sql synonym for each user table**/
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
ASSIGN
cTableName = _File-Name
cSQLSynonymName = QUOTER (cSQLSynonymOwnerName) + "." + QUOTER(cTableName).
PUT UNFORMATTED "DROP PUBLIC SYNONYM " cSQLSynonymName SKIP.
PUT UNFORMATTED "GO" SKIP.
PUT UNFORMATTED "CREATE PUBLIC SYNONYM " cSQLSynonymName " FOR PUB." QUOTER(cTableName) SKIP.
PUT UNFORMATTED "GO" SKIP.
PUT UNFORMATTED "GRANT SELECT ON " cSQLSynonymName " TO PUBLIC" SKIP.
PUT UNFORMATTED "GO" SKIP.
END.
OUTPUT CLOSE.