Finding a text in a Stored Procedure & User Defined Function
Because of the implementation of new error handling, i searched for the list of stored procedure having PRINT statement.I didn't find any simple method to find that list to replace the PRINT statement by RAISERROR statement.
After the analysis, i identified the simple solution as shown below.
Let's say you are searching for 'PRINT' in all your stored procedures. You can do this using the INFORMATION_SCHEMA.ROUTINES view, or syscomments:
SQL Query statement
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%PRINT%'
AND ROUTINE_TYPE='PROCEDURE'
We can use this for user defined function also.Let's say you are searching for 'RETURN' in all your user defined functions
SQL Query statement
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%RETURN%'
AND ROUTINE_TYPE='FUNCTION'
|
No responses found. Be the first to respond and make money from revenue sharing program.
|