Find Child Queries of a Stored Procedure

Currently Snowflake does not show the parent and child relationships between executed Stored Procedure and queries triggered from within the Stored Procedure from the QUERY_HISTORY view. This is quite an inconvenience if we want to find out what are the queries triggered by a certain Stored Procedure.

I found a workaround to this issue, though it is not perfect, it should work for the time being.

Run the following queries:

create or replace procedure sp_test()
returns string
language javascript
as 
$$
  snowflake.execute({sqlText: "select * from ericlin.test.test"});
  return 'true';
$$;

alter session set QUERY_TAG = 'sp_test';
call sp_test();
alter session set QUERY_TAG = default;

The trick here is to add a QUERY_TAG before calling the Stored Procedure, then reset it, so that the following queries won’t be wrongly tagged.

Then use the below query against QUERY_HISTORY to find the parent and child queries:

with parent_sp as (
    select regexp_replace(query_text, 'call (.*)\\(.*', '\\1') as key, *
      from table(information_schema.query_history())
    where query_text ilike 'call%'
),
child_sp as (
    select QUERY_TAG as key, *
      from table(information_schema.query_history())
    where query_text not ilike 'call %'
)
select
  p.query_id as parent_query_id, 
  p.query_text as parent_query_text, 
  c.query_id as child_query_id, 
  c.query_text as child_query_text
from parent_sp p
JOIN child_sp c
ON (p.key = c.key
   and p.start_time <= c.start_time
   and p.end_time >= c.end_time
   and p.session_id = c.session_id
);

The result looks like below:

***************************[ 1 ]***************************
PARENT_QUERY_ID   | 019fe893-0602-e072-0000-01d109aa67f2
PARENT_QUERY_TEXT | call sp_test();
CHILD_QUERY_ID    | 019fe893-0602-e072-0000-01d109aa67f6
CHILD_QUERY_TEXT  | select * from ericlin.test.test

Hopefully this can help!