Sunday, November 07, 2010

How do I find a stored procedure containing a particular 'text'?

Recently I have been assigned a task to find out all the stored
procs where we have put hardcoded values like 'AL', 'GL', 'TM' etc.

Checking all the stored procs one by one is a tedious task when
you have a few hundred of them in your application and there is a
possiblity of missing some stored procs where we have hardcoded
these values.

Following query I have used to find out all the stored procs where
we have hard coded all these kinda values.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' 'AL' '%'
AND ROUTINE_TYPE='PROCEDURE'

Similarly, we can check in the functions and views -

SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%AL%'

No comments: