Finding an object in server in SQL Server

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. I suggested her 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''
'
Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

  • Recent Posts

    • 450,719 hits
  • Join 37 other followers

  • Archives

  • Categories

  • July 2011
    M T W T F S S
    « Jun   Sep »
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
  • Advertisements
%d bloggers like this: