Sp_help for Oracle?

  • I'm building a set of SQL Server views that use OPENQUERY to run queries against an Oracle server.

    I want to see the metadata of the query from Oracle's perspective.

    Is there an equivalent to SQL Server's sp_help for Oracle that I could use?

    Gerald Britton, Pluralsight courses

  • you can use the two built in procedures which are used specifically for interrogating linked servers.

    EXEC sp_tables_ex [OracleLinkedServerName] will get you started, but from there, you need to find a database/schema/table to really dig into:

    EXECUTE sp_tables_ex

    @table_server ='CPULinked',

    @table_schema='BOOFILES',

    @table_name = 'DOCTR'

    EXECUTE sp_columns_ex

    @table_server ='CPULinked',

    @table_schema='BOOFILES',

    @table_name = 'DOCTR',

    @Column_Name ='DRTYPE'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks for the tip!

    Unfortunately, these queries returned empty result sets for me, though the server, schema and tables/views most certainly exist (and I can query them using OPENQUERY)

    Actually what I was looking for was an equivalent to sp_help that I can run on Oracle. So something like:

    select * from openquery(oracle, 'exec sp_help ''schema.table''')

    replacing 'exec sp_help ''schema.table''' with whatever the equivalent is on Oracle.

    Gerald Britton, Pluralsight courses

  • ahh, sorry.

    DESC or DESCRIBE TableName will get you some information, not sure how pretty it will be over a linked server, but that's what you want to try.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Alas, I don't think you can run DESC over OPENQUERY, since it is not a SQL command. I think you can only do that with the SQLPLUS command.

    Could be wrong though!

    Gerald Britton, Pluralsight courses

  • Doesn't Oracle have metadata views like ALL_TABLES, ALL_TAB_COLUMNS and ALL_USER_TABLES? Can you query those?

    Joie Andrew
    "Since 1982"

  • g.britton (5/15/2015)


    Alas, I don't think you can run DESC over OPENQUERY, since it is not a SQL command. I think you can only do that with the SQLPLUS command.

    Could be wrong though!

    You are right.....it is an internal command that only works with SQLPlus.....shame though....

  • This was removed by the editor as SPAM

  • have you tried transparent gateway between oracle and sql server?

    Cheers,
    John Esraelo

  • This was removed by the editor as SPAM

  • oracle has equivalent information_schema

    Select * from all_tables

    select * from all_views

    Select * from all_tab_columns

    ***The first step is always the hardest *******

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This reply has been reported for inappropriate content.

    Desc Table1 in Oracle will do this..

    Desc --->Describe

    Desc:

    1. will give definations of each column's name
    2. whether or not null values are allowed for each column
    3. datatype of columns, etc

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply