In db2 how to find all the Stored Procedures havin

Question

I want to find if a table is being used anywhere in all the stored procedures in a system. Is there a query to fetch all the details of SP.

Answer 1

The accepted answer didn't work for me for our particular flavor of DB2, but it set me in the right direction. Here is the query I wrote which allowed me to search sprocs in a given schema:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM sysibm.routines 
WHERE SPECIFIC_SCHEMA='<YourSchemaName>' 
    AND ROUTINE_DEFINITION LIKE '<YourSearchText>%'

Replace YourSchemaName and YourSearchText with appropriate values.

Answer 2

You can use SYSCAT.TABDEP and SYSCAT.ROUTINEDEP system catalog views.

For tables in Dynamic SQL statements, that are built and executed on the fly, you can use

select routinename,text from syscat.routines where language='SQL' and locate('<table-name>',text)>0

HTH

Sathyaram