I discovered this undocumented command one day when I was looking for a way to loop through a bunch (as in hundreds) of unattached SQL Server database and collect information about each one to put into a table. This was the perfect command to do that. It takes two parameters. The first is the path to a SQL Server datafile. The second is an ordinal, 0 to 3.
Option 0 returns one column, IsMDF. IsMDF is 1 if the file is the primary data file for the database, 0 if not. While the column name is IsMDF if you run it against an MDF file that isn’t the primary file it will still return false. I assume this comes from the suggested SQL Server naming convention that all data files but the primary use a .ndf extension.
Once you know that the data file is the primary data file option 1 will return info about the other files and file groups that are included in that database. This information can be used to create dynamic SQL to attach the database. Being able to determine this information dynamically is useful if the databases to be attached vary in the number, type and location of the secondary files. Running this against a data file that isn’t the primary file will produce an error. Option 3 produces an abbreviated version similar to Option 1.
Option 2 returns information about the database itself including the database name (which isn’t always the same as the file name), the database version (no need to try to attach a database that is a higher version or accidently upgrade a database by attaching it to a higher version), and the database collation.
The usually caveats apply when talking about undocumented commands. They generally shouldn’t be used in production.