Microsoft SQL Server: Accessing Registry using XPs 

While xp_regread read values from registry under exact path specified. To read instance specific registry entries from registry you can use xp_instance_regenumvalues andxp_instance_regread.

xp_instance_regread translates the given path to instance-specific path in the registry:

for example, executing following code against a SQL 2008 Instance returns the default database location which is specific to that instance:

DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread

       @rootkey      = N'HKEY_LOCAL_MACHINE',

       @key          = N'SOFTWARE\Microsoft\MSSQLServer\Setup',

       @value_name   = N'SQLDataRoot',

       @value        = @returnValue output

PRINT @returnValue

[EDIT: Above path returns the default installation data directory, to get default data directory use path: SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer]

this returns:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

Now, if I execute the same code against a SQL 2005 Instance on my system, it returns:

C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL

If the key path cannot be translated to instance-specific path, it will read from the specified key path.