Getting Information from a Database using Dynamic SQL

Steve Jones, 2013-05-20

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
GO
DECLARE @path VARCHAR(500)
   , @db VARCHAR(200)
;
SELECT @db = 'AdventureWorks2008'
IF EXISTS (SELECT name
         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:

dbfilepath

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

dbfilepath2

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)
DECLARE @file VARCHAR(500)
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)
DECLARE @file VARCHAR(500)
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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads