Accessing Registry using XPs (contd..) – TSQL
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 and xp_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:
— © 2011 – Vishal (http://SqlAndMe.com)
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.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
What if you have multiple instances (and versions) of SQL Server installed…how can you tell where the “default” data path is for each instance/version dynamically…without having to hard code the registry path
hmm.. that can be done, you can use xp_instance_regenumvalues to enumerate instance names and xp_regread to get data path. Give it a try..
I will also try the same and post it.
Hope this helps: https://sqlandme.com/2012/01/05/sql-server-identifying-default-data-directory-for-multiple-instances-through-registry/