Data Dictionary Queries

I often use the following queries when I need to find columns, tables, stored procs, views, etc. These queries are specific to Oracle, but they can easily be translated to other DB systems. The comments are self-explanatory, but if not, drop me a line, and I would be happy to clarify.

/**
 * Search All Table Columns
 */
SELECT *
FROM all_tab_columns ac
WHERE 1=1
AND ac.owner = 'SOME_SCHEMA_OWNER'
--AND ac.TABLE_NAME LIKE UPPER('%some_table_name%')
AND ac.column_name LIKE UPPER('%some_column_name%')
ORDER BY ac.table_name, ac.column_id;

/**
 * Search all primary keys and other constraints
 */
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner, cons.*
FROM all_constraints cons, all_cons_columns cols
WHERE 1=1
AND cols.table_name LIKE UPPER('%some_table_name%')
--AND cols.column_name LIKE UPPER('%some_column_name%')
--AND cons.constraint_type = 'P'
--AND cols.position = 1
AND cols.owner = 'SOME_SCHEMA_OWNER'
AND cons.status = 'ENABLED'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cons.constraint_type DESC, cols.position;

/**
 * Search All Index Columns
 */
SELECT i.table_name, i.index_name, i.column_position, i.column_name, i.*
FROM all_ind_columns i
WHERE 1=1
AND i.table_name LIKE UPPER('%some_table_name%')
--AND i.column_name LIKE UPPER('%some_column_name%')
ORDER BY i.table_name, i.index_name, i.column_position;

/**
 * Search custom codes.  This section is just an example of schema-specific codes.
 * Your DB MIGHT have something similar.
 */
SELECT *
FROM admincodes ac
WHERE 1=1
AND UPPER(ac.TABLENAM) LIKE UPPER('%some_table_name%')
AND UPPER(ac.columnnam1) LIKE UPPER('%typecd%')
--AND UPPER(ac.description) LIKE UPPER('%Unrelated%')
ORDER BY ac.tablenam, ac.columnnam1, ac.codevalue1;

/**
 * Search view definitions
 * This is a script, so in DataGrip, you have to click the Output tab and Enable the dbms_output icon for oracle
 */
declare
    cursor mycur is select vw.view_name, vw.text from dba_views vw where vw.owner NOT IN ('SYS','SYSMAN') AND rownum<862; --BUG: why does rownum have to be less than total rows???
    daname VARCHAR2(30);
    v varchar2(32000);
begin
    for c in mycur loop
        daname:=c.view_name;
        v:=c.text;
        if ( v like ('%case_sensitive_search_string%')) then -- THIS IS CASE SENSITIVE
            dbms_output.put_line('Search string found in view[' || daname || ']');
        end if;
    end loop;
end;


/**
 * Search view info.  Double-click WIDEMEMO to see TEXT definition
 */
select *
FROM DBA_VIEWS
Where 1=1 
AND owner NOT IN ('SYS','SYSMAN')
and VIEW_NAME  LIKE UPPER('%some_view_name%');

/**
 * Search stored procedure and package definitions
 */
select *
from user_source
WHERE UPPER(TEXT) LIKE UPPER('%search_string%')