My team and I were recently faced with a challenge that required us to search the database for a stored procedure that contains reference for a specific table or another stored procedure. We were able to accomplish that by a simple text search query that uses the system object definition and goes like that:
The interesting part begins once we were faced with a more complex system structure that included stored procedures which can be called from different databases and include inserts, updates and selects to yet another set of databases. Of curse executing the same query again and again for each database to perform the required search was not an option so the solution presented itself in an undocumented system stored procedure called sp_MSforeachtable.
This stored procedure allows us execute the same query for all the databases on the server with "?" used as database name. the final resulting query is the following:
If anyone can suggest a better more efficient way to perform that task please share in the comments.
SELECT OBJECT_NAME(object_id) FROM sys.sql_modules (nolock) WHERE definition like '%----SOME TEXT HERE----%'
The interesting part begins once we were faced with a more complex system structure that included stored procedures which can be called from different databases and include inserts, updates and selects to yet another set of databases. Of curse executing the same query again and again for each database to perform the required search was not an option so the solution presented itself in an undocumented system stored procedure called sp_MSforeachtable.
This stored procedure allows us execute the same query for all the databases on the server with "?" used as database name. the final resulting query is the following:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT "?" as DBName,OBJECT_NAME(object_id) FROM ?.sys.sql_modules (nolock) WHERE definition like ''%----SOME TEXT HERE----%'''
If anyone can suggest a better more efficient way to perform that task please share in the comments.
Comments
Post a Comment