Product:
Microsoft SQL server 2016
Issue:
When moving a SQL database to a different Windows server, you want to check the other database if they have any SP reference to that database.
Solution:
This query will loop all database and run the select statement on each, and find any store procedure that contain the word “foo”.
EXEC sp_MSforeachdb 'USE ? SELECT ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME, ROUTINE_DEFINITION, LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ''%foo%'' AND ROUTINE_TYPE=''PROCEDURE'' ORDER BY ROUTINE_NAME'
It is necessary to set the database for the query to run against, by using the USE ? statement, otherwise the code will execute in the context of the current database.
You can also use this to search inside one database:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%foo%' AND ROUTINE_TYPE='PROCEDURE' ORDER BY ROUTINE_NAME
or use below, as above have a limit of searching only the first 8000 characters of the stored procedure.
SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%foo%'
More information:
https://www.mytecbits.com/microsoft/sql-server/search-find-stored-procedure