--The first method: Query Dba_tab_columns
Copy Code code as follows:
Select Column_name,data_type,data_length
From Dba_tab_columns
where table_name =upper (' table name ')
ORDER BY column_name
--This method requires DBA authority
--The second method: 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 only finds tables under the current user
--The third method: 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 the table under all users
---------------------------Supplement-------------------------------------------------------------
Copy Code code as follows:
--Adding fields
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 table:
Select table_name from User_tables; --Current user's Table
Select table_name from All_tables; --All users ' tables
Select table_name from Dba_tables; --including system tables
Select table_name from dba_tables where owner= ' lbsp '; --Get the table owned by user * * The username here should be remembered in uppercase.
--Get table fields: In fact, this is based on the user's permissions to get the properties of the field (table name to capitalize)
SELECT * from user_tab_columns where table_name= ' user table ';--get all the fields in the user table and the properties of the fields.
SELECT * from all_tab_columns where table_name= ' user table ';--get all the fields in the user table and the properties of the fields. User is * * *
SELECT * from dba_tab_columns where table_name= ' user table ';--get all the fields in the user table and the properties of the fields. User is * * *
--Get Table comments:
SELECT * FROM User_tab_comments
--user_tab_comments:table_name,table_type,comments
The corresponding dba_tab_comments,all_tab_comments, these two more than User_tab_comments ower column.
--Get field comments:
SELECT * FROM User_col_comments
--user_col_comments:table_name,column_name,comments
The corresponding dba_col_comments,all_col_comments, these two more than User_col_comments ower column.
--Querying the index of all the users ' tables
SELECT * FROM User_indexes
--Query the index of the user table (nonclustered index):
SELECT * from user_indexes where uniqueness= ' nonunique '
--Query the primary key (clustered index) of the user table:
SELECT * from user_indexes where uniqueness= ' UNIQUE '
--Index of query 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 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 cu.table_name = ' NODE '
--Lookup Table uniqueness 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 '
--Find the foreign key of the table
SELECT * from user_constraints c where c.constraint_type = ' R ' and c.table_name= ' staffposition '
--Query the column name of the FOREIGN KEY constraint:
SELECT * from User_cons_columns cl where cl.constraint_name = FOREIGN key Name
--The column name of the key that queries the reference table:
SELECT * from User_cons_columns cl where cl.constraint_name = foreign key reference table key name
