SQL server root Directory

How can i Retrive Root Directory in SQ开发者_JS百科L Server using SQL Query???

Thanks.

The previous is returning the location for the SQL Client, which isn't really what you want. The following works in SQL 2005, 2008, and 2008 R2.

declare @SQLDataRoot varchar(256)
exec master..xp_instance_regread
   @rootkey='HKEY_LOCAL_MACHINE',
   @key='SOFTWARE\Microsoft\MSSQLServer\Setup',
   @value_name='SQLDataRoot',
   @value=@SQLDataRoot output

The undocumented xp_instance_regread correctly looks in the registry to get the SQLDataRoot variable for the SQL instance that you are querying - the path you see in the above query is not quite the true path in the registry. The XP replaces MSSQLServer with the version and instance name. For example, the true key on my instance is

SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.Inst2\Setup

In this case, I am running SQL 2008 R2 (turns into MSSQL10_50) with an instance name of Inst2.

The gist of it is this:

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]

Taken from here