SQL SERVER DBA, Linux and Azure: Finding an object in server in SQL Server using SP_MSFOREACHDB

Finding an object in server in SQL Server using SP_MSFOREACHDB

 Today one of my friend asked me that she has created a stored procedure in a database, suddenly she has recognized she has forgotten the database in which the procedure was created. We suggested him to use the below query which searches each and every database in the server and returns the database name and the object name that is specified in where clause.

SP_MSFOREACHDB searches the objects in all databases in the server.

Example:

SP_MSFOREACHDB
'
USE ?
select
    ''?'' Database_Name
   , Name Object_name
from sys.procedures
WHERE name LIKE ''USP_Test''

No comments:

Post a Comment