November 7, 2008 at 10:17 am
Is there a function I can use to get the database's name from the .mdf file in the directory without attaching it?
i.e. I was planning to use xp_dirtree to find all the .mdf files in a specified directory, then go through all the sysfiles views in each existing database and use those values to exclude the mdf files already attached to the database.
The next step is to build the SQL statement to attach the database. When I do this with CREATE DATABASE FOR ATTACH, I have to specify a name... which could be different from the filename...
So is there a way to query the database name contained within the .mdf file before I attach it so that I can get the right name for the CREATE DATABASE FOR ATTACH command?
November 7, 2008 at 1:34 pm
Ah Profiler is your friend!
Captured 2005's doing this when I profiled the attach of a database via the GUI.
declare @Path nvarchar(255)
declare @Name nvarchar(255)
declare @fileName nvarchar(255)
select @fileName = N'E:\mssql\data\testdata.mdf'
declare @command nvarchar(300)
select @command = 'dbcc checkprimaryfile (N'''+ @fileName + ''' , 2)'
create table #smoPrimaryFileProp(property sql_variant NULL, value sql_variant NULL)
insert #smoPrimaryFileProp exec (@command)
SELECT
p.value AS [Value]
FROM
#smoPrimaryFileProp p
drop table #smoPrimaryFileProp
Neat... another DBCC command that's undocumented in BOL.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply