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
