Sometimes there comes a scenario where we want to re-use something which is already used in stored procedure like certain function name, column name, parameter name, table name, joins and lots more of your required types from a list of stored procedures which needs a lot of work to discuss with any one senior who knows where it is used in the application or some concerned member who has worked on it.
But all of the above procedures are time intensive and it will cost you in time lost.
To overcome this today I will introduce you to the best of all things which will do all of your work in less time through script.
Let's discuss it in detail.
- SELECT ROUTINE_NAME, ROUTINE_DEFINITION
- FROM INFORMATION_SCHEMA.ROUTINES
- WHERE ROUTINE_DEFINITION LIKE '%KEYWORD%'
- AND ROUTINE_TYPE='PROCEDURE'
We are selecting two columns namely ROUTINE_NAME which is the name of the returned Stored Procedure or Function, ROUTINE_DEFINITION which contains the definition of the stored procedure like we use to get definiton of procedure by typing sp_helptext 'proc_name' with store procedure name.
The table view from which are we selecting the columns are INFORMATION_SCHEMA.ROUTINES which contain the backend logic of sys.objects and sys.columns so that it can fetch record easily.
Finally, the ROUTINE_TYPE which is the type through which you can get the definition like it only supports Stored Procedure and Function.
Returns PROCEDURE for stored procedures, and FUNCTION for functions.
You can check the
List of other ROUTES which can be used with ROUTE_DEFINITION Table.