3
0
Analyzing Table Usage in Stored Procedures: Imagine you’re a DBA responsible for a large Snowflake warehouse containing critical business data. This warehouse also houses numerous stored procedures used for various data manipulation tasks. As part of your data governance responsibilities, you’re tasked with evaluating current data retention practices. You lack a clear understanding of how tables are used within stored procedures. This makes it difficult to determine the appropriate retention period for each table.
By running FIND_TABLE_USAGE_IN_PROCS for specific schemas containing stored procedures, you can generate reports that reveal:
- Tables Referenced: The list of tables accessed by stored procedures within the chosen schema.
- Corresponding Procedures: The specific stored procedures that utilize each table. (Optional) Operation Types: If you modify the procedure with appropriate permissions, you can capture the operation type (SELECT, INSERT, UPDATE, CREATE) performed on the table within the procedure.
Based on the usage patterns and potentially the operation types (if captured), you can define data retention policies for each table.
- For frequently updated tables (INSERT, UPDATE), you might need longer retention periods to comply with regulations or support historical analysis.
- For read-only tables (SELECT) accessed for reporting or audits, you might define shorter retention periods based on compliance needs and data lineage requirements.
Procedure Details:
Call the Procedure:
CALL FIND_TABLE_USAGE_IN_PROC(‘PUBLIC’);
output:
The FIND_TABLE_USAGE_IN_PROCS stored procedure is a powerful tool for understanding table usage within stored procedures. By automating the process of analyzing stored procedures for table references, it saves time and reduces the risk of missing critical dependencies during schema changes.
