Obtain your SQL Server's database location
Sometimes your application needs to know the specific directory where your database resides. It's typically a front-end application that needs this information, but, occasionally, you may need it within a stored procedure. There are two ways to obtain this information: You can inspect the registry and get it there, or you can use T-SQL.
The value's actual registry location will depend upon your setup and the versions of SQL that you have installed. The tag you're looking for is SQLDataRoot. On my machine, it's located in the following node:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
If you need to know where the actual SQL engine is located, look at the tag SQLPath, which is also in this same node.
Note: Since the node name may differ depending on your version(s) of SQL Server, the simplest way to find it is to open regedit and search for SQLDataRoot.
Using T-SQL, there's an extended stored procedure called xp_instance_regread. To try it out, paste the following code into Query Analyser:
DECLARE @retvalue int, @data_dir varchar(500)
EXECUTE @retvalue
= master.dbo.xp_instance_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLDataRoot',
@param = @data_dir OUTPUT
PRINT 'SQL Server Data Path: '+ @data_dir
The code above calls the extended stored procedure explicitly (referring to the master database) based on the assumption that you'll be using the return value in the context of some other database.
As you can see from this query, the xp_instance_regread procedure can be used to obtain virtually any registry setting, and not just SQL-specific settings.