SQL Server - Querying instance information in Windows Registry using sys.dm_server_registry and xp_instance_regread

Reading Time: 3 minutes

Hello people,
Good afternoon!

In this post I will demonstrate how we can access the Windows registry and get useful information about the SQL Server instance.

If you would like to know how to query any Windows registry key as well as make changes to keys and values, please visit the post. SQL Server - How to read, list, create, change and delete Windows registry keys (Regedit) by CLR (C #)

Using the sys.dm_server_registry DMV

Available from SQL Server 2008 R2, the sys.dm_server_registry DMV allows you to easily access registry information for the SQL Server instance being queried.

The query is very simple:

And the query result will look something like this:

Now let's compare with the Windows Registry itself (RegEdit):

Note that DMV returns several different keys from the Windows registry and groups them into a single view, so that it makes life easier for the DBA and you don't have to look up multiple keys to get the information you need.

If we compare the records that are under the key “HKLM \ Software \ Microsoft \ Microsoft SQL Server \ MSSQL12.SQL2014 \ MSSQLServer \ Parameters”, we will see that they are the same values.

This is very useful for discovering some instance information, such as the port used, for example (I mentioned it in the post How to identify the port used by the instance of SQL Server)

Using the extended procedure xp_instance_regread

Available from SQL Server 2000, this extended procedure does not have much documentation on the Internet or by Microsoft itself (as well as most XP's), but it is very useful to retrieve and consult instance information in the Windows Registry, mainly because it is available in all versions of SQL Server to date.

Its use is quite simple, and unlike DMV, it only returns one record. Therefore, it is not possible to list the keys and values ​​at once, they must be queried manually. For this reason, DMV and this procedure have different concepts and purposes.

Example usage to return instance version:

Note: Remember that like other Extended Procedures, an OUTPUT variable with more than 4.000 characters is not allowed as a parameter to receive the returned data.

If an NVARCHAR (4001) or NVARCHAR (MAX) is entered, for example, the OUTPUT variable will generate an error message or be null (NULL) in the case of NVARCHAR (MAX).

Using the extended procedure xp_regread

Like XP xp_instance_regread, this procedure is available from SQL Server version 2000 to current versions and allows you to query any registry key for the server where the instance is installed.

Who has been following my blog for some time should already know XP xp_regread through the post The undocumented SQL Server extended procedures. If you have not accessed this post, do not waste time! Worth it.

The big difference between this XP and xp_instance_regread, is that xp_instance_regread "converts" the given path, turning "Microsoft \ MSSQLServer" into "Microsoft \ Microsoft SQL Server \ \ MSSQLServer ”.

Ex: If you enter “Software \ Microsoft \MSSQLServer\ MSSQLServer \ CurrentVersion \ ”, SP will convert to“ HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \Microsoft SQL Server \ MSSQL12.SQL2014\ MSSQLServer ”(in my case), which is the default installation location for the instance I'm connected to.

This is especially important when the server has more than one instance installed. With exactly the same code, you can query your instance keys without worrying about changing your code registry key for each instance.

Example of using xp_regread:

As you can see, using this SP we need to enter the full path of the registry key. However, it allows us to query any Windows registry key, such as the example below:

That's it, guys!
Thanks for stopping by and see you next post!