Dynamic Oracle SQL for Searching All Tables in a Schema for Specific Columns and Generating a Union Query

Long title – but it just about sums up what the end result was today.

I had this really annoying stacktrace with an associated exception that pointed to 400 potential tables.

I needed a way to check all those tables that match specific criteria where the length of the string was greater than 2000 characters and output those associated records for further debug analysis.

In this instance, break pointing and code tracing was not an option so I crafted a useful reusable query that can be used many times over for future circumstances.

If you are a user of this query – remember to turn DBMS Output on in Oracle SQL Developer or you will not see the query that is generated so you can copy it into an SQL Worksheet!

DECLARE
    -- Column we are looking for in each table of the schema
    COLUMN_TO_FIND VARCHAR2(256);

    -- Column to include so we can filter results for tables 
    -- that also contain this column as well - aka the table
    -- must contain all three columns
    PRIMARY_KEY_1 VARCHAR2(256);
    PRIMARY_KEY_2 VARCHAR2(256);

    -- Filter results to only the specified schemas so we 
    -- aren't querying system tables or other schemas
    SCHEMA_OWNER VARCHAR2(256);

    -- Helper Variable for Table Name in For Loop
    TABLE_NAME VARCHAR2(256);

    -- Helper Variable for Generation of Constant String Value 
    STATIC_COLUMN VARCHAR2(256);

    -- Helper Variable for Generation of Dynamic SQL Statement
    SQL_STATEMENT VARCHAR2(256);
BEGIN   

    -- To prevent buffer overflow
    dbms_output.enable(null);
    
    -- Edit these to your liking - read description above
    COLUMN_TO_FIND := 'DESCRIPTION';
    PRIMARY_KEY_1 := 'PRIMARY_KEY_1';
    PRIMARY_KEY_2 := 'PRIMARY_KEY_2';
    SCHEMA_OWNER := 'MY_COOL_ORACLE_SCHEMA';
    
    -- Loop through the results of the below SQL query to create 
    -- a query that is outputted into DBMS Output
    FOR item IN 
        (
            -- The query that identifies all the tables that contain the specified columns
            WITH COLUMN_TO_FIND_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = COLUMN_TO_FIND and owner = SCHEMA_OWNER), 
                 PRIMARY_KEY_1_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = PRIMARY_KEY_1 and owner = SCHEMA_OWNER), 
                 PRIMARY_KEY_2_TABLES AS (SELECT table_name FROM all_tab_columns WHERE column_name = PRIMARY_KEY_2 and owner = SCHEMA_OWNER)
            SELECT table_name 
            FROM COLUMN_TO_FIND_TABLES 
            WHERE table_name IN (SELECT * FROM PRIMARY_KEY_1_TABLES) AND 
                  table_name IN (SELECT * FROM PRIMARY_KEY_2_TABLES)
        ) 
    LOOP

        -- Redeclare variable to make things cleaner - personal preference
        TABLE_NAME := item.table_name;
    
        -- Declare the table_name as a static column for the union statement 
        -- so we know where the result is coming from if we want to analyze
        -- the specific record that is being outputted via the union
        STATIC_COLUMN := '''' || TABLE_NAME || '''' || ' AS table_name'; 
        
        -- Build the dynamic sql query that will be outputted into DBMS 
        -- so that we can copy it into an SQL worksheet after it finishes
        --
        -- The final query that is built will display all records where "COLUMN_TO_FIND"
        -- has a length greater than 1000 characters
        SQL_STATEMENT := 'select ' || PRIMARY_KEY_1 || ', ' || PRIMARY_KEY_2 || ', ' ||  STATIC_COLUMN || ' from ' || TABLE_NAME || ' where LENGTH(' || COLUMN_TO_FIND || ') > 1000';
        
        -- Output to DBMS Output for copy paste into SQL Worksheet later
        dbms_output.put_line(SQL_STATEMENT);
        dbms_output.put_line('UNION');
    
    END LOOP;
END;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s