July 22, 2009 at 12:26 am
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
July 22, 2009 at 1:23 am
google for sp_Msforeachdb. It will definitely help you.
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 22, 2009 at 3:54 am
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 ?"
July 22, 2009 at 4:58 am
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
July 24, 2009 at 9:27 pm
exec sp_MSforeachdb "select '?' As DatabaseName,name from ?.dbo.sysobjects where name in ('Tablename')"
MJ
July 25, 2009 at 7:10 am
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy