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 Analyzer:

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.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!