oracle uses sql to obtain database tables and fields of tables

--Method 1: Query dba_tab_columns

 
select COLUMN_NAME,DATA_TYPE,DATA_LENGTH 
from dba_tab_columns 
where table_name =upper(' Table name ') 
order by COLUMN_NAME 

-- This method requires a DBA Authority  

-- No. 1 2 Methods :  Query user_tab_cols 
select COLUMN_NAME,DATA_TYPE,DATA_LENGTH 
from user_tab_cols 
where table_name=upper(' Table name ') 

order by COLUMN_NAME 
-- This method can only find tables under the current user  

-- No. 1 3 Methods :  Query ALL_TAB_COLUMNS 
select distinct COLUMN_NAME,DATA_TYPE,DATA_LENGTH 
from ALL_TAB_COLUMNS 
WHERE TABLE_NAME= upper(' Table name ') 
-- This method can query tables under all users  

--------------------------------------------------------------------------------------------------

 
-- Add a field  
alter table cw_srcbpb 
add (SRCBPB_RJBPBL varchar2(100) ); 
alter table cw_srcbpb 
modify (SRCBPB_RJBPBL number(30,3) ); 
--Oracle View all tables and fields  

-- Get the table:  

select table_name from user_tables; -- Table for the current user  
select table_name from all_tables; -- Tables for all users  
select table_name from dba_tables; -- Including system tables  

select table_name from dba_tables where owner='LBSP'; -- Get users *** The user name of the table you own here should remember to be capitalized.  
--  Getting table fields: In fact, the attributes of the fields are obtained according to the user's permissions (the table name should be capitalized)  

select * from user_tab_columns where Table_Name=' User table ';-- Gets all the fields of the user table and the properties of the fields.  

select * from all_tab_columns where Table_Name=' User table ';-- Gets all the fields of the user table and the properties of the fields. To which the user is *** 

select * from dba_tab_columns where Table_Name=' User table ';-- Gets all the fields of the user table and the properties of the fields. To which the user is *** 

-- Get table comments:  

select * from user_tab_comments 

--user_tab_comments : table_name,table_type,comments 

-- Correspondingly, there are also dba_tab_comments , all_tab_comments , these two ratios user_tab_comments More ower Column.  

-- Get field comments:  
select * from user_col_comments 
--user_col_comments : table_name,column_name,comments 
-- Correspondingly, there are also dba_col_comments , all_col_comments , these two ratios user_col_comments More ower Column.  
-- Query the indexes of all tables of the user  
select * from user_indexes 
-- Query the index of the user table ( Nonclustered index ): 
select * from user_indexes where uniqueness='NONUNIQUE' 
-- Query the primary key of the user table ( Clustered index ): 
select * from user_indexes where uniqueness='UNIQUE' 
-- Index of lookup table  
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and 
t.table_name='NODE' 
-- Query the primary key of the table  
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and 
au.constraint_type = 'P' AND cu.table_name = 'NODE' 
-- Lookup table only 1 Sexual constraints (including names, constituent columns):  
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name=au.constraint_name and 
cu.table_name='NODE' 
-- Foreign key for lookup table  
select * from user_constraints c where c.constraint_type = 'R' and c.table_name='STAFFPOSITION' 
-- Column name of query foreign key constraint:  
select * from user_cons_columns cl where cl.constraint_name =  Foreign key name  
-- Column name of the key that queries the reference table:  
select * from user_cons_columns cl where cl.constraint_name =  Key name of foreign key reference table