Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

to which DB these tables belong Expand / Collapse
Author
Message
Posted Wednesday, July 22, 2009 12:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:57 PM
Points: 310, Visits: 650
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
Post #757164
Posted Wednesday, July 22, 2009 1:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:52 PM
Points: 530, Visits: 1,134
google for sp_Msforeachdb. It will definitely help you.

Thanks
Chandra Mohan
Post #757187
Posted Wednesday, July 22, 2009 3:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:57 PM
Points: 310, Visits: 650
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 ?"
Post #757274
Posted Wednesday, July 22, 2009 4:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 2, 2014 12:02 AM
Points: 185, Visits: 726
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
Post #757292
Posted Friday, July 24, 2009 9:27 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
exec sp_MSforeachdb "select '?' As DatabaseName,name from ?.dbo.sysobjects where name in ('Tablename')"


MJ
Post #759572
Posted Saturday, July 25, 2009 7:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 2, 2014 12:02 AM
Points: 185, Visits: 726
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
Post #759619
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse