How to find the SQL Server install directory from tsql

How do we find the SQL Server install directory using a T-sql command or from command prompt?

Thanks,
Vivek
You can find the path (with and without filename) to master.mdf from the master_files system view, if that's enough:

Code Snippet

select physical_name
from master.sys.master_files
where name = 'master';

declare @.tail int
set @.tail = (
select charindex('\',reverse(physical_name))
from master.sys.master_files
where name = 'master'
)
select substring(physical_name,1,len(physical_name)-@.tail)
from master.sys.master_files
where name = 'master';

The installation directory is a registry key, and a batch file to dig it out is suggested here: http://weblogs.asp.net/jgalloway/archive/2006/10.aspx. (I didn't try it, but there's a link to another page noting that the white space after delims is a tab followed by a space.)

Steve Kass
Drew University
http://www.stevekass.com
|||

All you need is in the link below but in C#. It is 23pages long so read it but you need admin permissions if it is a server. Hope this helps.

http://msdn2.microsoft.com/en-us/library/bb264562.aspx

|||The system databases are not under the SQL Server installed directory. They are in a different drive. If this is the case how do i get the install directory?
|||

One more approach on T-SQL itself..

Code Snippet

Create Table #Data

(

path Varchar(max)

)

Insert Into #Data

Exec xp_cmdshell 'path'

Declare @.Paths Varchar(max)

Set @.Paths = ';'

Select @.Paths = @.Paths + Replace(Path,'Path=','') From #Data Where Path is NOT NULL

select @.Paths = @.Paths + ';'

Create table #Number

(

Number Int

)

Declare @.i as Int

Set @.i = 1

While @.i<len(@.Paths)

Begin

Insert Into #Number Select @.i;

Set @.i=@.i+1

End

Select

Replace(Replace(paths,'\Tools\BINN\',''),'\Tools\BINN','') [SQL Server Paths]

From

(

Select

Substring(@.Paths,Number,Charindex(';',@.Paths,Number)-Number) Paths

From

#Number

Where

Substring(@.paths,Number-1,1) = ';'

) as Data

Where Paths Like '%Microsoft SQL Server%'

Drop Table #Number;

Drop Table #Data;

|||

One more easy approach .. using undocumented system procedure..

Code Snippet

Declare @.Path as varchar(100);

Set @.Path = NULL

Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\70\Tools\ClientSetup', 'SQLPath', @.Path OUTPUT

Select @.Path as [Sql Server 7.0 path]

Set @.Path = NULL

Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup', 'SQLPath', @.Path OUTPUT

Select @.Path as [Sql Server 2000 path]

Set @.Path = NULL

Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup', 'SQLPath', @.Path OUTPUT

Select @.Path as [Sql Server 2005 path]

Set @.Path = NULL

Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup', 'SQLPath', @.Path OUTPUT

Select @.Path as [Sql Server KATMAI path]

|||

You could use SMO and write a CLR function that would return the value.

It could be more reliable (thinking permission issues) than reading the registry with xp_regread.

For an idea, refer to Books Online, Topic: 'RootDirectory property'

There are several properties of the Server object that one could find useful.

|||Thanks Manivannan it works!!!
|||

The link below contains a SQLCLR TVF that can be used to get this information and others which are persisted in the registry only. For SQL Server 2000, you will have to access the registry keys directly from a batch file or use system databases path (which may be configured during setup to different location).

http://blogs.msdn.com/sqltips/archive/2005/08/19/SqlRegSettings.aspx