Do you need to find out where the location of
your SQL Server's database is? Use this quick tip from Arthur Fuller to show you
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:
If you need to know where the actual SQL engine is located, look at the tag
SQLPath, which is also in this same
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
Using T-SQL, there's an extended stored procedure called
xp_instance_regread. To try it out, paste the following code into Query
DECLARE @retvalue int, @data_dir varchar(500)
@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.