Recently, I tried how to use "Object Search". This is what I did:

From the "Object Search" window, I typed:

Object name: sp_lock
Datase: <all> (this is default, I didn't change it)
Hint limit: 100 (thisis default, I didn't change it)

Then I put checks on:
system table
user table
View
Store procedure
user defined function

Then I clicked on "Find now" button, and it came up "1 row".

Now my question is how do I know "where" is this sp_lock ??? in Master? or in Pubs? How can I tell where it is???

Thanks for advice.

__________________
cocolocopolo

Report this post to a moderator

My QA shown the [db name] column which is 'master', And all sp_% (system stored procedure) should be in 'master'.

Report this post to a moderator

Thanks prajuab.

Some of sp are in Master, but some are not.

What I try to understand is, "how" do we
know in Master?

By experience ??? or I have to post each sp
in the forum for help ???

Thanks again.

__________________
cocolocopolo

Report this post to a moderator

I tried this feature too. However, when I did it in query analyzer, instead of just coming up with "1 row affected" I actually got the row data itself. The row listed the database it was found in, the object owner, object name, and object type. Is there another place to use object search? Maybe I just used it in a different place. Try it in query analyzer and see what comes up.

Report this post to a moderator

Thanks necrophantasm.

This is what I tried to understand:

Before you an execute stored procedure in Editor
Pane (Query Analyzer), you have to select the
correct database.

Suppose I am going to execute sp_add_operator.

But before I can type "Exec sp_add_operator", I have to select the correct database.

How do I know sp_add_operator is under master?
or under Pubs? or under ???

So, I tried use "Object Search" to find out
where is the sp_add_operator.

If I use "Object Search", it only comes up (1 row). But it did not show up "where" you can find
the sp_add_operator.

Without selecting the correct database and try to
execute this procedure only got error "Could not find stored procedure 'sp_add_operator"

__________________
cocolocopolo

Report this post to a moderator

If you do the object search with the database setting to "all" then in the db_name column of the result set it should list the location of the stored procedure (in my case it was in the MSDB database). However, if there were multiple instances of the same object, I think it would list exactly which databases had the object. Hope this helps a little

Report this post to a moderator

Dear necrophantasm:

I am sorry. I still cannot get it.

This time I typed in sp_renamedb, then click
"Find now".

Yes, it comes up (1 row). But where is this
sp??? Under master??? or under ???

__________________
cocolocopolo

Report this post to a moderator

I will look into it and let you know. Maybe if I give you step by step instructions on how I did it you will get the same response.

Report this post to a moderator