System Procedures for Obtaining Linked-Server Metadata

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 serversp_foreignkeys 'oradb',NULL,'scott'  -- shows all foreign keys  in SCOTTsp_primarykeys 'oradb','dept','scott'  -- shows the primary key  for DEPTsp_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.