Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at savvy@sqlmag.com.
How can I use T-SQL to list which files belong to which filegroups for a particular database?
A simple solution is to run sp_helpfile, which shows information about the files in a database. But if you want to know the T-SQL SELECT queries for a custom application, look at the T-SQL code in the sp_helpfile procedure. You'll be amazed at how much advanced T-SQL you can learn by reading system procedures.
Another solution to your problem is to let SQL Server Profiler find the answer for you. When you want a particular piece of information from Enterprise Manager but don't know a T-SQL query that will display the information, simply run Profiler while you perform your task in Enterprise Manager. Profiler will display the T-SQL queries that Enterprise Manager generates to provide the information. Enterprise Manager issues T-SQL queries to get information about the filegroup that a file belongs to, then displays that information in the Properties dialog box for the database you're working in. To find a T-SQL query that answers your question, I ran Profiler, navigated to the DataFiles tab of the Properties dialog box for a database, and watched the queries that were executed. Listing 1 shows a T-SQL query that I found.