0

I know this SQL query provides the table structure:

DESC TableName;

But do we have a query which will provide table structure of all database tables in one go/single query/command? BTW, I am using SQL Developer.

Thank you in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ABG
  • 17
  • 1
  • 6
  • SQL Developer is a client, can you confirm you're querying an Oracle database too? Also `desc[ribe]` is not SQL, it's a client command (that queries the data dictionary in the background...). What are you going to use the output for - just to browse as text? – Alex Poole Feb 26 '19 at 19:08
  • Incidentally, since you're using SQL Developer, if you open the log (Ctrl-Shift-L in Windows) ans switch to its 'Statements' tab, then describe any table, you'll be able to see the exact query the client is running to generate the output you see. (Double-click a statement to see it in full in a pop-up window). That might be a starting point for your own multi-table query? – Alex Poole Feb 26 '19 at 19:15
  • Thank you @AlexPoole, yes i am using Oracle database, as i am working on peoplesoft platform. let me check your suggestion. – ABG Feb 27 '19 at 11:30

2 Answers2

1

The answer can be found here. You can query 

SELECT * FROM ALL_TAB_COLUMNS

or

SELECT * FROM ALL_TAB_COLS

Difference between ALL_TAB_COLUMNS and ALL_TAB_COLS

Johann
  • 349
  • 2
  • 9
  • Thank you Johann both queries worked. and thank you for sharing what the difference between those 2 statements. – ABG Feb 27 '19 at 11:31
1

Not a command, but if you're in the GUI, getting all that information back in a single query result set could be a challenge to process, at least for this human it is.

So instead, you could also build a Data Dictionary report, complete with Referential Integrity diagrams - not sure if your peoplesoft box will actually have any foreign keys, but it's worth a shot.

File > Data Modeler > Import > Data Dictionary

Point to your existing SQL Developer Oracle Database connection.

Select the SCHEMA you want to pull from - check all if you want everything, but be prepared to wait for awhile, better to limit yourself to the application schemas.

If you're just interested in tables, just pick those, but I'm guessing VIEWs would also be of interest - there's a checkbox on the bottom to quick toggle everything ON.

Click Finish.

When you're done, you'll have a design and your data model. You'll see the diagram.

enter image description here

You can then go to File > Data Modeler > Reports

Choose either the Tables or Table Views report.

Set your format, and click 'Generate Report'

enter image description here

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120