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:
Post a Comment