Data Management

Obtain your database's location within a stored procedure

There may be times when your application needs to know the specific directory where your database resides. If you need this information within a stored procedure, find out two ways to obtain the 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 Analyzer:

DECLARE @retvalue int, @data_dir varchar(500)
EXECUTE @retvalue = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
'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!

Editor's Picks

Free Newsletters, In your Inbox