Oracle query table information, including table name, field name, field type, primary key, FOREIGN key Uniqueness constraint information, index information query SQL as follows, we hope to help:
1, check out all the user tables
SELECT * FROM User_tables can query all user tables
Select Owner,table_name from All_tables; Querying all tables, including other user tables
Filtering by table name requires that the letters be treated as follows
SELECT * FROM user_tables WHERE table_name = upper (' Table name ')
Because the name of the table name is uppercase or lowercase when you create the table, the TABLE_NAME field in the corresponding User_tables table automatically becomes uppercase after the CREATE statement is executed, so you must upper the string into uppercase by using the built-in function to query Otherwise, even after the build table statement is passed, the corresponding record is still not queried through the query statement above.
2, query the index of all the tables of the user
SELECT * FROM User_indexes
3. Query the index of the user table (nonclustered index):
SELECT * from user_indexes where uniqueness= ' nonunique '
4. Query the primary key of the user table (clustered index):
SELECT * from user_indexes where uniqueness= ' UNIQUE '
5. 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 '
6. 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 '
7, find the uniqueness of the table constraints (including the name, the constituent column):
Select column_name from User_cons_columns cu, user_constraints au where cu.constraint_name=au.constraint_name and
Cu.table_name= ' NODE '
8. 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
Query the column name of the key referencing the table:
SELECT * from User_cons_columns cl where cl.constraint_name = foreign key reference table key name
9. Querying all columns of the table and their properties
Method One:
SELECT * from User_tab_columns where table_name=upper (' table name ');
Method Two:
Select Cname,coltype,width from col where Tname=upper (' table name ');;
10. Query for procedures and functions that exist in a user select Object_name,created,status from user_objects where Lower (object_type) in (' Procedure ', ' function '); 11. Querying permissions for other role tables select * from Role_tab_privs;
View index number and category
SELECT * from user_indexes where table_name= ' table name ';
View the fields indexed by the index
Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');
Ps:
View constraints on a table
Sql>select constraint_name, Constraint_type,search_condition, R_constraint_name
From user_constraints WHERE table_name = UPPER (' &table_name ');
Sql>select C.constraint_name,c.constraint_type,cc.column_name
From User_constraints c,user_cons_columns cc
where C.owner = Upper (' &table_owner ') and c.table_name = Upper (' &table_name ')
and C.owner = Cc.owner and c.constraint_name = Cc.constraint_name
Order BY Cc.position;
View the name of the view
Sql>select view_name from User_views;
Oracle Query table information (indexes, foreign keys, columns, etc.)
