location of table of sql server

Take a look at this tip: Use T-SQL to View Database Information[^] and use sp_help[^] to get informations about database objects.

you can find the location using following query

SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

Resultset:

DataFileLocation<br />
--------------------------------------------------------------------------<br />
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\<br />
(1 row(s) affected)

Tables and indexes are stored in filegroups, not files directly. Each filegroup may contain several files. To query which table is in which filegroup, you can use:

select so.name, sfg.name
from sys.sysobjects so, sys.indexes si, sys.filegroups sfg
where si.data_space_id = sfg.data_space_id
and so.id = si.object_id

and to list the files for filegroups

select * from sys.sysfiles