Skip navigation

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 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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.