How do I find a table name in a stored procedure\view in snowflake with snowsql?
The SQL statement in SQL Sever equivalent is:
select distinct
[Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
from
sys.objects o
inner join
sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
inner join
sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P', 'FN')
where
o.name = 'YourTableName'
order by
sp.Name
Snowflake stored procedure definitions are stored in an object in the shared database Snowfalke, in schema Account_Usage and in table Functions.
select * from functions where upper(function_definition) like '%TABLENAME%' and function_language = 'JAVASCRIPT';
It's not exact but should get you close to where you want to be.
As mentioned in the other answers there isn’t a direct equivalent. The following is a hacky and error prone way to achieve something similar.
SELECT procedure_schema,procedure_name, procedure_definition
from information_schema.procedures where rlike(procedure_definition,'.\bTABLE_NAME\b.','si');
Very similar to the previous answer, but here I am using the information_schema.procedures. I am also using regular expression to match only the table name and avoid partial matches. Also notice the s and i parameters in rlike the s is used to allow . in regex to match new lines and the i is just to make the pattern case insensitive.
INFORMATION_SCHEMA. @FelipeHoffa - I believe the question is asking how to find stored procedures that reference a particular table. – Mike Walton