Oracle Query table information (indexes, foreign keys, columns, etc.)

Oracle Query table information (indexes, foreign keys, columns, etc.)

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, the query out all the user table select * from User_tables can query out all the 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 user all table index 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. Primary Key of query 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 Andcu.table_name= ' NODE '

8. Lookup table FOREIGN Key select * from user_constraints c where c.constraint_type = ' R ' and c.table_name= ' staffposition '

Query column name for 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 the Foreign key reference table

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. Querying 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_na me = cc.constraint_name order by cc.position;

View the name of the view

Sql>select view_name from User_views;