Sp_help for Oracle?

Question

Sp_help for Oracle?

g.britton

SSChampion

Points: 13863
More actions
May 15, 2015 at 6:11 am

#317501

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

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

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

Lowell SSC Guru Points: 323526 More actions · Answer 1

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!

g.britton SSChampion Points: 13863 More actions · Answer 2

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

Lowell SSC Guru Points: 323526 More actions · Answer 3

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!

g.britton SSChampion Points: 13863 More actions · Answer 4

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

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 5

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

Joie Andrew
"Since 1982"

kevaburg SSCoach Points: 18131 More actions · Answer 6

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

John Esraelo-498130 SSCertifiable Points: 5916 More actions · Answer 7

have you tried transparent gateway between oracle and sql server?

Cheers,
John Esraelo

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 8

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

MarieWalker Grasshopper Points: 15 More actions · Answer 9

This reply has been reported for inappropriate content.

Desc Table1 in Oracle will do this..

Desc --->Describe

Desc:

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

Sp_help for Oracle?

Cookies on SQLServerCentral