Read registry from SQL Server with xp_instance_regread and find the path of your SQL Server installation

Some machines are really mess up with couple of successful and unsuccessful installation/un-installation of SQL Server and the result is you can find program files/Microsoft SQL Server folder in couple of drive. If you want to find current running installation of SQL Server than there are few different ways. Out of which I am going to show you couple of different approaches.

Method 1:

You can query sysaltfiles system catalog resides in MASTER database. You can see the path of system databases especially MASTER database. Most probably master database would be in the same folder of SQL Server.

Here is the TSQL:

select * from master..sysaltfiles

Method 2:

There is a system stored procedure in Master database, named “xp_instance_regread” which can help you to read registry.  There is a specific location in system registry which will give you path of the installation directory. This method is more appropriate than previous one. Here the code snippet of the same.

declare @regreader int, @directory nvarchar(4000)

exec @regreader = master.dbo.xp_instance_regread

     N'HKEY_LOCAL_MACHINE',

     N'Software\Microsoft\MSSQLServer\Setup',

     N'SQLPath',

      @directory output, 'no_output'

select @directory AS InstallationDirectory

GO

If the user you are using while running above snippet doesn’t have permission to read registry, you might greeted with permission error.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com