Do you ever need to query for the DefaultData & DefaultLog folders for your SQL Server instance? Well, I just did and the following script enabled me to do that:

DECLARE @HkeyLocal NVARCHAR(18),@MSSqlServerRegPath NVARCHAR(31),@InstanceRegPath SYSNAME;

SELECT @HkeyLocal=N'HKEY_LOCAL_MACHINE'
SELECT @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
SELECT @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'

DECLARE @SmoDefaultFile NVARCHAR(512)
EXEC MASTER.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultData', @SmoDefaultFile OUTPUT
DECLARE @SmoDefaultLog NVARCHAR(512)
EXEC MASTER.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @SmoDefaultLog OUTPUT

SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]'

I haven’t done any rigorous testing or anything like that, all I can say is…it worked for me (on SQL Server 2012). Use as you see fit.

Doubtless this information exists in a multitude of other places but nevertheless I’m putting it here so I know where to find it in the future.

Just for fun I thought I’d try this out against SQL Azure Windows Azure SQL Database. Unsurprisingly it didn’t work there:

Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in 'MASTER.dbo.xp_instance_regread' is not supported in this version of SQL Server.

@Jamiet