CODEWITHSUNDEEP
sqloracle-databasesqlitedata-dictionary
Akira
Akira

Reputation: 2870

An equivalence of ".schema sqlite_master" in Oracle

I'm trying to do the same exercise for both SQLite and Oracle. In SQLite, there is a table sqlite_master containing a description of all of the other tables, indexes, triggers, and views that are contained within the database. I can see the query to generate sqlite_master with .schema sqlite_master.

In Oracle, the data dictionary is presented to us in the form of a number of views (DBA, ALL or USER). Let's take the table USER_TABLES as an example. We can do query on USER_TABLES, for example

SELECT table_name
FROM USER_TABLES;

Is there anyway to get the query used to create the table USER_TABLES in Oracle? I tried

SELECT dbms_metadata.get_ddl('TABLE', 'USER_TABLES')
FROM dual;

but it does not work.

Upvotes: 0

Views: 217

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

I am not sure what are you looking for but if you want the definition of any table in oracle you can use :

Describe TableName

Or if you want to have list of tables, views or columns you can use below queries: For Tables :

select * from select * from all_tables

For Columns :

select * from all_tab_columns

For Views :

select * from select * from all_views

To get all the column information of a table: select *

 from all_tab_columns
 where upper(table_name) = upper('Test')
 order by column_id

Typically oracle stores table_name in uppercase so I used upper() or you can just type 'TEST'

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59622

You could use this one:

DECLARE
   DDL  CLOB;
BEGIN
   FOR aTab IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
      DDL := DBMS_METADATA.GET_DDL('TABLE', aTab.TABLE_NAME);
      DBMS_OUTPUT.PUT_LINE(DDL);
   END LOOP;
END;

You may customize the output with DBMS_METADATA.SET_TRANSFORM_PARAM() before you run the query.

Upvotes: 1

Related Questions