• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

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)
0
allelopath
Asked:
allelopath
  • 4
  • 3
3 Solutions
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now