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.
