How do I get the *actual* default database location in SQL Server?

Question

In SQL Server Management Studio, there is an option to set the default database path for a given instance:

This works. When I say

CREATE DATABASE test

it gets created in the path I specify, E:\data. Great.

But how do I get this path in T-SQL (for use in setup scripts)?

Contrary to what many pages say, there is no registry key (on my machine) for DefaultData or DefaultLog. I am running SQL Server 2005 Express on XP 64.

This value has got to be stored somewhere. Where?

Answer 1

Use SYS.DATABASE_FILES:

SELECT df.physical_name
  FROM SYS.DATABASE_FILES df

SYS.DATABASE_FILES.type_desc

• ROWS = mdf location, includes mdf filename
• LOG = ldf location, includes ldf filename

Answer 2

Do you have access to SQL Profiler?

I don't have SQL 2005 Express installed but when I view that properties page on my machine with Profiler running I can see where SQL Server retrieves it from on my SQL 2008 instance.

declare @SmoDefaultFile nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', 
  @SmoDefaultFile OUTPUT

select ISNULL(@SmoDefaultFile,N'') AS [DefaultFile]

Edit: The above also works for me on SQL Server 2005 Developer edition but only after I changed the default path to something other than the default.