SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

Filed under: Blog Tagged: syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...