SQL Server Performance Forum – Threads Archive

Database tables – Physical locations

I’d like to know how to find out the physical locations of database tables. Is there a way to determine on what physical disk(s) a table is located?
Thanks!

The tables belong to some filegroup. If you only has one filegroup, said in D:, then the table is in D:. Now if you have some RAID, then you only know in witch logical disk that table are stored, but no physical.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Well I have multiple database files in one file group that resides on one RAID LUN and I’m trying to find out what tables are in what file. So for example I’m using database generator application to build a sample database and I have a xxx.mdf and a xxx.ndf file and the tables get distributed across the data files. I’d like to be sure where specific tables are being place but I’m not sure how to tell. Any ideas?

If you have RAID, you only now that any tables are in xxx.mdf or xxx.ndf but no in witch physical disks are.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Here you go: Just replace ‘YOURTABLE’ with the name of the table you want to check and it should give you the actual physical file name. DECLARE @Filenum tinyint
DECLARE @FirstPage binary(6)
DECLARE @FString varchar(14)
SET @FirstPage=(select [first] from sysindexes where id = object_id(‘YOURTABLE’) and indid in (1,0))
EXEC [master].[dbo].[xp_varbintohexstr] @FirstPage, @Fstring OUTPUT
SET @FileNum=(SELECT CONVERT(tinyint,SUBSTRING(REVERSE(@Fstring),2,2)))
SELECT filename FROM sysfiles WHERE [email protected] Nathan H. Omukwenyi
MVP [SQL Server]

That almost worked. I’m using SQL Server 2005 and it didn’t find the stored procedure xp_varbintohexstr. I searched around for a while and found the function fn_varbintohexstr but it has some incorrect parameters. I’ll play around with it and see if I can get it to work. In the meantime any advice?

I assumed you were using SQL 2000 since you posted in this forum. Anyway to achieve the same thing in SQL 2005, use the following: For Non-Partitioned Tables DECLARE @first binary(6)
SELECT @first = first from sysindexes where id = object_id(‘YOURTABLE’) and indid in (1,0)
DECLARE @FileNum int
SELECT @FileNum = convert(int, substring(@first,6,1) + substring(@first,5,1))
SELECT physical_name from sys.database_files where [email protected]
For partitioned tables:
SELECT physical_name from sys.data_spaces ds
JOIN sys.destination_data_spaces dds ON dds.data_space_id=ds.data_space_id
JOIN sys.indexes i ON dds.partition_scheme_id=i.data_space_id
JOIN sys.database_files df ON df.data_space_id=ds.data_space_id
WHERE i.object_id=object_id(‘YOURTABLE’)
ORDER BY ds.name Just remember to replace YOURTABLE with the table in question. Nathan H. Omukwenyi
MVP [SQL Server]

That was it! Thanks a ton!
Cheers!

The pleasure was actually mine. You could have saved yourself a tonne of trouble by posting in the SQL 2005 forum. To cover all your tables you should actually create a stored procedure and pass the table name as a parameter. Nathan H. Omukwenyi
MVP [SQL Server]

]]>