Locate a table to a database

I find some tables like so:                                                              
                                                                                         
select table_name                                                                        
from information_schema.tables                                                            
where table_name like '%MyTable%'                                                        
                                                                                         
The results look like:                                                                    
1 tblMyTableOld                                                                          
2 tblMyTable1                                                                            
3 tblMyTable2                                                                            
                                                                                         
I want to find the location of tblMyTable2 (ie which database it is in). How do I do this?
                                                                                         
SQL Server 2008 R2                                                                        
SQL Server Management Studio (10.50.2500.0)
LVL 1
allelopathAsked:
Who is Participating?
 
ralmadaCommented:
To start with information_schema.tables will list tables from the current database, so if you want to know the current database name, use db_name()

for example you can do

select db_name() as dbname, table_name                                                                        
from information_schema.tables                                                            
where table_name like '%MyTable%'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
There will be one or more physical files where the table reside, its not usuall to have separate physical files for each table. the easiest option to find this location is by running a sql statement

sp_HelpDB 'yourdatabaseName'

or right click the databsae - properties -> files
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the MDF /ndf contain the table data,
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ralmadaCommented:
@aneesh, I think he's talking about the database not the physical file, unless I misunderstood the question...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
@ralmada,
I also think so, thats why i suggested sp_helpDB
0
 
allelopathAuthor Commented:
ralmada:
When I try the command you suggest, I get 3 columns. The first column has no header text, the next have "dbname" and "table_name", but there is no data in the columns.
0
 
ralmadaCommented:
I get 3 columns

There're only two columns in the query I've posted above. Can you post the exact query you're running?


but there is no data in the columns.

So probably that table doesn't exist in that database?
0
 
ralmadaCommented:
Now if what you want is to search for that table in each database then try

exec sp_msforeachdb 'use ?; select ? as dbname, table_name                                                                         
			from information_schema.tables                                                            
			where table_name like ''%MyTable%'''

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.