CODEWITHSUNDEEP
sqloracle-databaseschema
devio
devio

Reputation: 1169

Oracle : which SQL command to get all details about a table?

I am developing an Oracle database schema visualizer. So, as a first step, I thought, I will first need to get all the schema details (tables and relationships between tables, constraints also maybe).

To get that information, what is the SQL command that will return the result?

(the DESCRIBE command does not return the information about the all the table)

EDIT #1:

Actually, what I want to do is to get all the information about all tables as I mentioned (columns, rows, foreign keys, constraints) to store them ina MongoDB database, and then create visualizations (diagrams are not included)

Upvotes: 6

Views: 45522

Answers (3)

Magier
Magier

Reputation: 517

I do something similar. I read those things from a SQL Server over OPENQUERY statements directly from Oracle DBs and save results into SQL Server tables to allow analysis of historic comparison schema information and changes.

So what you have to do with the resultsets of the following queries is to store them (regulary) somehow and add some kind of unique / primary key or timestamp to it, in order to distinguish between your different scans.

Leaving away the SQL Server specific code stuff, those are the basic oracle sql queries I use so far:

--Tables
SELECT table_name, owner, Tablespace_name, Num_Rows 
FROM all_tables WHERE tablespace_name is not NULL 
AND owner not in ('SYS', 'SYSTEM') 
ORDER BY  owner, table_name;

--Columns
SELECT OWNER, TABLE_NAME, Column_name, Data_type, data_length, data_precision, NULLABLE, character_Set_Name  
From all_tab_cols 
where USER_GENERATED = 'YES' 
AND owner not in ('SYS', 'SYSTEM');

--Indexes
select Owner, index_name, table_name, uniqueness,BLEVEL,STATUS from ALL_INDEXES
WHERE owner not in ('SYS', 'SYSTEM') 

--Constraints
select owner, constraint_name, constraint_type, table_name, search_condition, status, index_name, index_owner 
From all_constraints 
WHERE generated = 'USER NAME'
AND owner not in ('SYS', 'SYSTEM') 

--Role Previleges
select grantee, granted_role, admin_option, delegate_option, default_role, common
From DBA_ROLE_PRIVS

--Sys Privileges
select grantee, privilege, admin_option, common
From DBA_SYS_PRIVS

Upvotes: 12

Sualeh Fatehi
Sualeh Fatehi

Reputation: 4784

MongoDB supports JSON documents, so the best way to solve your issue is to extract database metadata into JSON docuemnts. You can use SchemaCrawler to extract database metadata as JSON.

Sualeh Fatehi, SchemaCrawler

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

You could start with:

select DBMS_METADATA.GET_DDL ('TABLE', table_name,user) from user_tables;

That will reverse engineer all the DDL that would create the tables. It wouldn't give you current table/column statistics though.

Some tools (eg Oracle's Data Modeller / SQL Developer) will allow you to point them at a database and it will reverse engineer the database model into a diagram. That would be a good place to start for the relationships between tables.

Upvotes: 1

Related Questions