SQL query to pull all database table structure in one go?
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
How do I write the table structure in Oracle from an existing schema?
Call dbms_metadata
in your query on user_tables
:
select dbms_metadata.get_ddl('TABLE',table_name,user)
from user_tables;
`show create table` equivalent in oracle sql
If you are asking about SQL*Plus commands (show create table table_name
doesn't appear to be a SQL statement), you can use the desc
command
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
If you really want a SQL statement, you can use the dbms_metadata
package
1 select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' )
2* from dual
SQL> /
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
ALTER INDEX "SCOTT"."PK_EMP" UNUSABLE ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
CACHE
Depending on the tool you are using, you may need to run set long 10000
first, that tells SQL*Plus to display the first 10,000 bytes of any LOB that is selected. If your DDL is longer, set a larger value.
Is there a way to view relationships in Oracle SQL Developer?
It's not clear if you're looking for a GUI solution, but you can query the information from the dictionary by:
select table_name from user_constraints
where r_constraint_name in
(select constraint_name
from user_constraints
where constraint_type in ('P','U')
and table_name = upper('&tableOfInterest')
)
Oracle : which SQL command to get all details about a table?
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
SLECT 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
How do I view a Tree list of Schemas for a database in Oracle SQL Developer?
Expand the "Other Users" node.
Related Topics
Add Single Quotes to Results in a Column from a SQL Query
Count All Records Per Day in a Specific Month
How to Check If Value Is Inserted Successfully or Not
How to Get the Last 12 Months from the Current Date
How to Make a Join of 3 Tables in Jpa
How to Select Last 5 Rows in a Table Without Sorting
Detect If Value Is Number in MySQL
Sql Query to Select from 1 Hour Ago
Spark - Query Dataframe Based on Values from a Column in Another Dataframe
Sql to Generate a List of Numbers from 1 to 100
Display Each Department's Number and Name and the Number of Employees Employed in Each Department
How to Get Last 7 Days Data from Current Datetime to Last 7 Days in SQL Server
Select Distinct Values from One Table and Join With Another Table
How to Convert Milliseconds to Time(Hh:Mm:Ss) in Oracle
How to Merge Multiple Rows into Single in Oracle
Sql Server Function to Return Minimum Date (January 1, 1753)