SQL Object Search

While working with large database where there are hundreds of stored procedures, it is very difficult to do changes in schema. The schema change might cause chages in some or many stored procedures.

In such case if we have simple query that gives all the procedure names that contains the given text.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_DEFINITION LIKE '%give the search string here%' AND ROUTINE_TYPE='PROCEDURE'

The above query will give the list of the stored procedure name (along with the text) that contains the given string.

This can be really handy when working with large database.

Bharat