Extract SQL queries from Snowflake Stored-Procedure using Snowflake-JDBC API

Question

I have requirement to extract SQL queries from snowflake stored procedure. I have decided to extract SQL queries via Snowflake-JDBC API.

I have analyzed Java documentation of Snowflake-JDBC API but unfortunately could not find any methods to extract SQL queries from stored procedure. I found a class namely QueryExecDTO in Snowflake-JDBC API , which has getSqlText() method but it is of no use in my concern (I have to extract SQL from stored procedure). I am also aware of Snowflake-JavaScript API's Statement object , which has method getSqlText() to get text of SQL queries but it can be use inside JavaScript only as it is part of JavaScript-API

Is there any way to extract SQL from stored procedure using Snowflake-JDBC API?

Answer 1

You would need to run something like:

select get_ddl('procedure', '*proc_name*(*arg list*)');

To get the text of the SP and then you would need to parse that text to extract the SQL statements.

If you want to just extract the SQL statements that should be relatively straightforward; however if you want to parse the statements to, for example, list the tables being used, then you are going to struggle.

Parsing SQL is incredibly complex (given how flexible the language is) which is illustrated by the fact that there are very few general SQL parsers available - and those that actually work are not cheap.