CODEWITHSUNDEEP
javadatabaseoracle-databasejdbcmetadata
lunicon
lunicon

Reputation: 1740

How to get VIEW references (base tables) through JDBC?

When I create VIEW, database engine pases my query and store it some how. Can I then find out what tables are used by one view? I understand that different databases may be differently. May have some sort of level of abstraction throw JDBC.

Something tells me that this is done through getTables(?):

Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPASS);        
DatabaseMetaData md = con.getMetaData();
md.getTables(null, null, null, null);

If it can not be. Are there solution in particular for Oracle database (like view/referenses in plsql developer)?

Upvotes: 3

Views: 6943

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109257

As far as I know there is no JDBC specific way to retrieve this information: you will need to dive into your database specific system tables and see if that information is available.

The rest of this answer describes how to retrieves view and does not answer the question:

DatabaseMetaData#getTables() with type "VIEW"

For example

dmd.getTables(catalog, schema, "%", new String[] {"VIEW"});

Upvotes: 4

xQbert
xQbert

Reputation: 35343

Oracle:

Select * from dictionary

Returns many system tables/functions you have access to which contain valuable structure information.

in that list you will see

Select * from ALL_Views (which contains the source SQL for the view) which could be parsed for table names.

Though I think there's an all_Dependencies or all_References view that will contain required references to tables in seperate fields which would be easier to parse I'm looking for that now...

Select * from All_dependencies WHERE Name = 'YOUR_VIEWNAME' and Referenced_Type='TABLE' will return all referenced tables.

Though I'm not sure how those features work with Linked tables. so if you're referencing external tables though a linked server... those features may not work.

Upvotes: 3

Related Questions