Users who query database objects in Oracle

Users who query database objects in Oracle

Users who query database objects in Oracle

-- Query tables and views of a user
SELECT TABLE_NAME, OWNER, TABLE_TYPE FROM ALL_TAB_COMMENTS where owner = 'vass ';

-- Query views of a user
SELECT VIEW_NAME, owner from ALL_VIEWS where owner = 'vass ';

-- Query the index of a certain use
SELECT INDEX_NAME, OWNER, TABLE_NAME FROM ALL_INDEXES where owner = 'vass ';

-- Query triggers of a user
Select owner, OBJECT_NAME, OBJECT_TYPE FROM ALL_PROCEDURES
Where owner = 'vass 'AND OBJECT_TYPE = 'trigger ';
-- Or
SELECT TRIGGER_NAME FROM ALL_TRIGGERS where owner = 'vass ';

-- Query functions of a user
Select owner, OBJECT_NAME, OBJECT_TYPE FROM ALL_PROCEDURES
Where owner = 'vass 'AND OBJECT_TYPE = 'function ';

-- Query stored procedures of a user
Select owner, OBJECT_NAME, OBJECT_TYPE FROM ALL_PROCEDURES
Where owner = 'vass 'AND OBJECT_TYPE = 'Procedure ';
-- Or
SELECT PROCEDURE_NAME, OBJECT_NAME, OBJECT_TYPE FROM ALL_PROCEDURES where owner = 'vass ';

-- Query the sequence of a user
SELECT SEQUENCE_NAME, SEQUENCE_OWNER FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = 'vass ';

 

-- Query tables and views of all users
SELECT * FROM ALL_TAB_COMMENTS;

-- Query the user's tables and views
SELECT * FROM USER_TAB_COMMENTS;

-- Query the column names and comments of all user tables.
SELECT * FROM ALL_COL_COMMENTS;
 
-- Query the column name and comment of the user's table
SELECT * FROM USER_COL_COMMENTS;

-- Query the column names and other information of all user tables (detailed but no remarks ).
SELECT * FROM ALL_TAB_COLUMNS;

-- Query the column name and other information of the user's table (detailed but no remarks ).
SELECT * FROM USER_TAB_COLUMNS;
 
Select t. TABLE_NAME, T. comments from USER_TAB_COMMENTS T;

SELECT R1, R2, R3, R5
FROM (select a. TABLE_NAME R1, A. COLUMN_NAME R2, A. COMMENTS R3 FROM USER_COL_COMMENTS ),
(Select t. TABLE_NAME R4, T. COMMENTS R5 FROM USER_TAB_COMMENTS T)
WHERE R4 = R1;
 

-- How to query the table names, primary key names, indexes, and foreign keys of all user tables in ORACLE
 
-- 1. query all indexes of a table (including index names, types, and columns ):
Select t. *, I. INDEX_TYPE FROM USER_IND_COLUMNS T, USER_INDEXES I
Where t. INDEX_NAME = I. INDEX_NAME and t. TABLE_NAME = I. TABLE_NAME AND T. TABLE_NAME = 'tablename ';
-- 2. query the table's primary key (including the name and column composition ):
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 = 'tablename ';
 
-- 3. uniqueness constraints of the query table (including names and columns ):
SELECT COLUMN_NAME FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
Where cu. CONSTRAINT_NAME = AU. CONSTRAINT_NAME and au. CONSTRAINT_TYPE = 'U' and au. TABLE_NAME = 'tablename ';
-- 4. 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 = 'tablename ';
-- Query the names of Columns with foreign key constraints:
SELECT * FROM USER_CONS_COLUMNS cl where cl. CONSTRAINT_NAME = 'pkname ';
-- 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 keyname ';
-- 5. query all the columns and their attributes of the 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 = 'tablename ';