How to find related database objects?

Question

hello,
We are using SQL Server 2000 and
is there a way to find all the database objects by supplying a table/view/stored procedure name?
My problem is I have couple of tables and views which are used across different database in a server. So I want to list all the database objects using these tables and views across all the database.
Is it possible? Any suggestions??  SP_Depends only helps to some degree.

Answer 1

--The following query lists all database containing table TB:
EXEC SP_MSFOREACHDB 'USE ? IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME=''TB'') PRINT ''?'''

Answer 2

happyflystone :Surprise! You are here also! Thanks for promosing my answer!

Answer 3

it's not working as intended. When I give the table name and if the table is uesed in multiple database then it gives me only one database.
suppose there is a table1 in db1 which is used in db2 and db3
but when i execute this statement it gives me only db1.
so any suggestions?

Answer 4

I ran the code above and it worked for me (I change the query to Name like ''T%'')

Do you have all these databases in the same server?

---

Premature optimization is the root of all evil in programming.

Donald Knuth, repeating C. A. R. Hoare

My blog

Answer 5

CSDYYR: I have similar problem so I tried your query. It doesn't work if the table is used while creating a view in other database. I modified your query to work for view
EXEC SP_MSFOREACHDB 'USE ? IF EXISTS(select * from sysobjects a join syscomments b
on a.id = b.id
where b.text like ''%TB%'') PRINT ''?'''

but the problem is if there is a table with the same name in another database then this query doesn't work.
any suggestions????