to which DB these tables belong

Question

Post reply

to which DB these tables belong

luckysql.kinda

SSCertifiable

Points: 7816
More actions
July 22, 2009 at 12:26 am

#140020

I have three tables and I need to find to which DB these tables belong. I am not sure how to do this. There are 29 DBs and to do task manually is a really bad idea.
I used sp_help 'tablename' -- No use
select * from sysobjects where name = 'tablename' --No use
-LK

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

google for sp_Msforeachdb. It will definitely help you.

[font="Verdana"]Thanks
Chandra Mohan[/font]

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 2

Following is my learning today. MS_Foreachtable can execute more than one command simultaneously.

exec sp_MSforeachtable 'select ''?'', count(1) from ?'

exec sp_MSforeachtable "print '?' DBCC DBREINDEX('?')"

exec sp_MSforeachtable "print '?' SELECT count(1) from ? SELECT TOP 1* FROM ?"

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 3

luckysql.kinda (7/22/2009)
I have three tables and I need to find to which DB these tables belong. I am not sure how to do this. There are 29 DBs and to do task manually is a really bad idea.
I used sp_help 'tablename' -- No use
select * from sysobjects where name = 'tablename' --No use
-LK

Hey This works for you

exec sp_MSforeachdb " print '?' select name from sys.sysobjects where name in ('Table1','Table2','Table3')"

Rajesh Kasturi

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

exec sp_MSforeachdb "select '?' As DatabaseName,name from ?.dbo.sysobjects where name in ('Tablename')"

MJ

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 5

MANU (7/24/2009)
exec sp_MSforeachdb "select '?' As DatabaseName,name from ?.dbo.sysobjects where name in ('Tablename')"
MJ

hey manu

thanks for modifying the query........

cheers.......

Rajesh Kasturi