SQL Server data dictionary information is stored in the SQL Server database as SQL Server system tables.

All SQL Server system tables have names prefixed with sys. The SQL Server system tables define the structure of a database. When you change data definitions, SQL Server reads and modifies the SQL Server system tables to add information about the user tables.

Accessing a gateway data dictionary table or view is identical to accessing a data dictionary in an Oracle database.

SQL> SELECT * FROM "ALL_CATALOG"@MSQL;

1. Maps the requested table, view, or synonym to one or more SQL Server system table names. The gateway translates all data dictionary column names to their corresponding SQL Server column names within the query. If the mapping involves one SQL Server system table, the gateway translates the requested table name to its corresponding SQL Server system table name within the query. If the mapping involves multiple SQL Server system tables, the gateway constructs a join in the query using the translated SQL Server system table names.
2. Sends the translated query to SQL Server.
3. Might convert the retrieved SQL Server data to give it the appearance of the Oracle database data dictionary table.
4. Passes the data dictionary information from the translated SQL Server system table to the Oracle database.

   Note:

   The values returned when querying the gateway data dictionary might not be the same as the ones returned by the Oracle SQL*Plus DESCRIBE command.

Queries issued directly to individual SQL Server system tables are allowed but they return different results because the SQL Server system table column names differ from those of the data dictionary view.

Also, certain columns in an SQL Server system table cannot be used in data dictionary processing.

The Oracle Database Gateway for SQL Server supports the following views and tables.

No other Oracle database data dictionary tables or views are supported. If you use a view not on the list, you will receive the Oracle database error code for no more rows available.

Queries through the gateway of any data dictionary table or view beginning with ALL_ can return rows from the SQL Server database even when access privileges for those SQL Server objects have not been granted. When querying an Oracle database with the Oracle data dictionary, rows are returned only for those objects you are permitted to access.