Oracle obtains table structure information: Table Name, view or not, field name, type, length, non-empty, primary key, and oracle View
select a.TABLE_NAME as "TableName", case when (select count(*) from user_views v where v.VIEW_NAME =a.TABLE_NAME )>0 then 'V' else 'U'end as "TableType", a.COLUMN_NAME as "ColumnName", A.COLUMN_ID as "ColumnIndex", a.DATA_TYPE as "DataType", case when a.DATA_TYPE = 'NUMBER' then case when a.Data_Precision is null then a.Data_Length else a.Data_Precision end else a.Data_Length end as "Length", case when a.nullable = 'N' then '0' else '1' end as "IsNullable", b.comments as "Description", case when (select count(*) from user_cons_columns c where c.table_name=a.TABLE_NAME and c.column_name=a.COLUMN_NAME and c.constraint_name= (select d.constraint_name from user_constraints d where d.table_name=c.table_name and d.constraint_type ='P') )>0 then '1' else '0'end as "IsPK" from USER_TAB_COLS a, sys.user_col_comments b where a.table_name = b.table_name and b.COLUMN_NAME = a.COLUMN_NAME order by a.TABLE_NAME, a.COLUMN_ID
Query all the field names in the table with known table names in oracle. Check whether each field is a primary key, a foreign key, or an empty SQL statement,
Query all the columns and their attributes of a table:
Select t. *, c. COMMENTS
From user_tab_columns t, user_col_comments c
Where t. table_name = c. table_name and t. column_name = c. column_name and t. table_name = women;
Query the table's primary key:
Select cu .*
From user_cons_columns cu, user_constraints au
Where cu. constraint_name = au. constraint_name and au. constraint_type = 'p' and au. table_name = women;
Search for the table's foreign key (including the name, referenced table name, and corresponding key name. The following is a multi-step query ):
Select * from user_constraints c where c. constraint_type = 'r' and c. table_name = women
Query the names of Columns with foreign key constraints:
Select * from user_cons_columns cl where cl. constraint_name = foreign key name;
Query the column name of the key of the referenced table:
Select * from user_cons_columns cl where cl. constraint_name = foreign key reference table key name;
Obtain basic information about each column in a specified table in oracle
Method 1:
Run the following command in Oracle SQL Plus:
DESC table name;
Method 2:
Use the PLSQL Developer tool, right-click the table name, click Edit, and select columns]
Or enter the DESC table name in the PLSQL Developer command window;