I am working within a snowflake DB schema that has about 200 tables and I need to figure out which table has a specific row-level value within a column. Looking for any table that contains a value = “Alternative Credential” in the rows. I executed this, but it stopped working after the second run:

CREATE OR REPLACE PROCEDURE find_text_occurrences()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    table_name STRING;
    column_name STRING;
    all_columns CURSOR FOR (
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE table_schema NOT IN ('INFORMATION_SCHEMA', 'YOUR_EXCLUDED_SCHEMA') 
        AND data_type = 'TEXT'
        -- Add more filters if needed
        -- AND table_name IN ('YOUR_TABLE_1', 'YOUR_TABLE_2') -- Filter tables
    );
BEGIN
    -- Create or recreate the temporary table
    CREATE OR REPLACE TEMPORARY TABLE discovery_results (
        table_name STRING,
        column_name STRING,
        matches INT
    );

    -- Process columns and tables
    FOR record IN all_columns DO
        table_name := record.table_schema || '.' || record.table_name;
        column_name := record.column_name;

        EXECUTE IMMEDIATE 
            'INSERT INTO discovery_results (table_name, column_name, matches) ' ||
            'SELECT ''' || table_name || ''', ''' || column_name || ''', COUNT(*) ' ||
            'FROM ' || table_name || ' ' ||
            'WHERE ' || column_name || ' LIKE ''%Alternative Credential%'''
        ;
    END FOR;

    RETURN 'Results are available in the discovery_results table. Run SELECT * FROM discovery_results to find the results';
END;
$$;

CALL find_text_occurrences();

SELECT * FROM discovery_results;

On first run, it worked–displaying table and column name and how many row-level matches. When I changed the value to something else, like “Marketing”, it displayed

002003 (42S02): SQL compilation error:
Object 'DISCOVERY_RESULTS' does not exist or not authorized.

Any idea what I’m doing wrong here?