Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » to which DB these tables belong
Add to briefcase

to which DB these tables belong Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
luckysql.kinda
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: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645
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
Chand00
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: Saturday, March 02, 2013 4:42 AM
Points: 525, Visits: 1,096
google for sp_Msforeachdb. It will definitely help you.

Thanks
Chandra Mohan
Post #757187
luckysql.kinda
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: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645
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
Rajesh kasturi
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: Tuesday, April 16, 2013 12:26 PM
Points: 185, Visits: 725
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
MANU-J.
Posted Friday, July 24, 2009 9:27 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 1,654, Visits: 8,563
exec sp_MSforeachdb "select '?' As DatabaseName,name from ?.dbo.sysobjects where name in ('Tablename')"


MJ
Post #759572
Rajesh kasturi
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: Tuesday, April 16, 2013 12:26 PM
Points: 185, Visits: 725
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.