find table name in which database 1

[hokky]

Hi guys,

Just quick question,

in SQL 2000
Is there any quick way to search particular table name in which database, without going to database one by one.

Because what I'm currently doing is like this

use [database name]
Select * From sysobjects Where name like '%TABLENAME%'

And I need faster way.

Thanks guys

 

[dgillz]

You are very close, try this:

SELECT     *
FROM         sysobjects
WHERE     (name LIKE '%TABLENAME%')

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Sweenster]

If you are just trying to find which db a table lives in then you should be able use Object search..
In Query Analyzer, Press F4, enter criteria and do 'Find Now'

 

[hokky]

Thanks guys,

I'll try it tomorrow

 

[gmmastros]

I also suggest you try....

sp_msforeachdb 'If Exists(Select * from [?].information_Schema.Tables Where Table_Name like ''%TABLENAME%'') Select ''?'' As DatabaseName '

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom