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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: