Click here to Skip to main content
15,175,525 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to know the location of table of sql server,how can i get the location and path of stored table in sql server
Posted

Tables are stored in the MDF file, they do not have file locations as they are all in one big file.
   
Take a look at this tip: Use T-SQL to View Database Information[^] and use sp_help[^] to get informations about database objects.
   
v2
you can find the location using following query


SQL
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)
   
v2
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:
SQL
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
SQL
select * from sys.sysfiles
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900