Getting Information from a Database using Dynamic SQL

I ran across someone that was building a restore script to automated their restores. This person wanted their script to work with any instance, and that means they’d need to find the path for the database files, if the database already existed.

It was interesting to me, and I decided to give a solution a try, and I ended up using dynamic SQL, which I don’t love, but it worked. As I was digging through, I realized that all the database data is in sys.master_files. However I’d started this and it was an intriguing problem. I don’t love this solution, and I wouldn’t use it here, but there might be a place where you can use it.

To find out if a database exists, you can easily use the sys.databases view to find it.

USE master
   , @db VARCHAR(200)

SELECT @db = 'AdventureWorks2008'

         FROM sys.databases
 WHERE name = @db

This is a snippet, and you need more code for this to work, but it does work if you include the “then” and “else” blocks. If the database doesn’t exist, you can grab the default file paths from the registry if you want to build the restore script, but if it does, then what.

You want to find the database files, but these aren’t stored in master. If you query sys.database_files, you’ll get this:


I have 8 or 10 databases on this instance, but none appear. However if I query a specific database, I get the files.


How do I get this data, in a script, given that I can’t execute a “use” statement easily at runtime.

There are probably a few ways, but for me, I decided dynamic SQL might make sense her. This is an administrative task, so it’s not likely to allow for SQL Injection as I wouldn’t expose this for users to run.

The first step is to build my query. In this case, I want to execute this query:

 select physical_name
  from AdventureWorks2008.sys.database_files
   where file_id = 1

This isn’t ideal, in that I could have many files for this database, but for now I’m concerned with just getting the primary data file.

I can build this string dynamically like this. Note that I’ve assigned the result to a variable for now.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @db varchar(75)
SET @db = 'AdventureWorks2008'
SET @sqlCommand = 'select @f = physical_name from ' + @db + '.sys.database_files where file_id = 1'

SElect @sqlCommand

However now I need to run this command and return a value. sp_executesql is a function that allows you to execute a string, pass in parameters, and assign them back. I can do this with this script.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @db varchar(75)

SET @db = 'AdventureWorks2008'
SET @sqlCommand = 'select @f = physical_name from ' + @db + '.sys.database_files where file_id = 1'

EXECUTE sp_executesql @sqlCommand, N'@f varchar(500) OUTPUT', @f=@file OUTPUT

select @file

If I run this, I’ll get the file path from the AdventureWorks2008 primary data file.

That’s the first step in this process. If I wanted to complete it, I’d have to make sure I did this for each data file, probably using some temporary table instead of a variable, and storing all the physical paths and logical names, and using those to build a dynamic restore script.

Or I could download this script: SQL 2005 Restore Script Generator

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

3 Responses to Getting Information from a Database using Dynamic SQL

  1. Just for fun (I like dynamic SQL) I wrote a script to dynamically generate the move statement all at once. Saves the effort of going back and getting the info for each individual file.

    DECLARE @dbName nvarchar(50)
    SET @dbName = ‘AdventureWorks2008’
    DECLARE @MoveString nvarchar(max)
    DECLARE @sql nvarchar(max)
    SET @sql =
    N’SELECT @MoveString = (SELECT ”MOVE N”””+ name + ””” TO N””” +
    physical_name + ”””” + ”,” + CHAR(13)
    FROM ‘+@dbName+’.sys.database_files names
    FOR XML PATH(””),TYPE).value(”.”,”VARCHAR(MAX)”)’

    PRINT @sql

    EXEC sp_executeSql @sql, N’@MoveString nvarchar(max) OUTPUT’, @MoveString OUTPUT

    PRINT @MoveString

  2. Vin DiPippo says:

    To make life a little easier in situations like this, there is an object named sys.master_files that gives you all of the files for all of the databases in one place.

    It is used quite often to join with the results of something like sys.dm_io_virtual_file_stats but would also be a better option here.

    That’s not to say that you don’t need Dynamic SQL for lots of other things, but this consolidated object is quite handy.

  3. way0utwest says:

    Thanks, I found master_files after I wrote this Felt a little stupid I hadn’t noticed it before, but it didn’t stand out to me.

Comments are closed.