Find Physical Location of Filegroup on SQL 2008 using TSQL

Question

How can I use tsql to find the physical location of a filegroup on SQL Server 2008?

Answer 1

Try this..

SELECT * FROM sys.database_files

OR

SELECT * FROM sys.sysaltfiles

Also
Use this script to see a more detailed picture of objects and their actual physical file names and locations.

SELECT    OBJECT_NAME(i.id) AS Table_Name
        , i.indid           AS Index_ID
        , i.name            AS Index_Name
        , i.groupid         AS Group_ID
        , f.name            AS File_Group
        , d.physical_name   AS [File_Name]
        , s.name            AS Dataspace
FROM        sys.sysindexes      i
INNER JOIN  sys.filegroups      f   ON f.data_space_id = i.groupid
INNER JOIN  sys.database_files  d   ON f.data_space_id = d.data_space_id
INNER JOIN  sys.data_spaces     s   ON f.data_space_id = s.data_space_id
WHERE   OBJECTPROPERTY(i.id, 'IsUserTable') = 1