If you aren't comfortable using Oracle tools to query the Oracle data dictionary, some SQL Server system stored procedures can help you obtain Oracle metadata from the linked Oracle server. The following procedures accept from four to seven parameters. They all accept the same first three parameters (linked-server name, table name, schema name).

                              sp_indexes 'oradb'  -- shows all indexes on the linked                                 Oracle server                              sp_foreignkeys 'oradb',NULL,'scott'  -- shows all foreign keys                                in SCOTT                              sp_primarykeys 'oradb','dept','scott'  -- shows the primary key                                for DEPT                              sp_tables_ex 'oradb'  -- shows all tables, views, and synonyms                                on the linked Oracle server

SQL Server Books Online (BOL) gives full details about the use of these procedures.

In addition, SQL Server's sp_columns_ex system procedure, which returns a list of columns in an Oracle table, requires at least three non-null parameters:

                              sp_columns_ex 'oradb','dept','scott' -- all columns in the                               DEPT table in SCOTT

The sp_linkedservers procedure, which returns a list of all linked-server definitions, takes no parameters. The sp_catalogs system procedure works when your linked servers are both SQL Server, but it doesn't work with Oracle linked servers. For details about calling system procedures, see BOL; go to the Index tab and enter the name of the procedure you want to know more about.