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;
One thought on “Dynamic Oracle SQL for Searching All Tables in a Schema for Specific Columns and Generating a Union Query”
This is a perfect use case for SchemaCrawler scripting, using a technique similar to the one described in https://dev.to/sualeh/how-to-generate-mermaid-diagrams-for-your-database-33bn