Is there a query to use in Snowflake to retrieve the original SELECT statement behind the creation of a table?

Question

I am trying to find the original code behind the creation of a table in Snowflake.

I am using this query:

SELECT GET_DDL('table', 'table1');

This is only giving me the original DDL behind the table. I would need the full code (as in the original SQL SELECT statement).

Anyone know what query could get me that?

Answer 1

You can query QUERY_HISTORY and get the SQL statement (and other data) using the following:

// Be sure to use a role with permission to perform the following

SELECT
    *
FROM
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
    QUERY_TEXT ILIKE '%create%table%table1%'
ORDER BY END_TIME DESC
LIMIT 20;