CODEWITHSUNDEEP
sqlstored-proceduresdb2
dynamoG
dynamoG

Reputation: 39

In db2 how to find all the Stored Procedures having a given text in it

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.

Upvotes: 1

Views: 17774

Answers (2)

Hoodlum
Hoodlum

Reputation: 1503

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.

Upvotes: 3

Sathy Sannasi
Sathy Sannasi

Reputation: 181

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

Upvotes: 6

Related Questions