Trying to capture Names of .MDF files to table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to capture Names of .MDF files to table

Author

Topic

tcarnahan
Starting Member

23 Posts

Posted - 2008-07-18 : 17:37:22

I am attempting to write some code where I extract the
names of all .MDF files and insert them into a temp
table.

I can achieve the results I need if I "hard-code" the
path to the .MDF files, however, I need that path
to be a variable. The following code works. The
second set of code (which is my goal) does NOT work:


SET NOCOUNT ON
GO

DECLARE @cmd as nvarchar(255)

-- Note: I hard-coded the path to the .MDF files. The path contains a dollar sign ("$") which is correct.
SET @cmd = 	'master..xp_cmdshell ' + CHAR(39) + 
		' dir /B ' + CHAR(34) + 'D:\Program Files\Microsoft SQL Server\MSSQL$MTB_2005\data\*.mdf' + 
		CHAR(34) + CHAR(32) + CHAR(39)

PRINT '@cmd = ' + CONVERT(nvarchar(255),@cmd)

EXEC (@cmd)

/* Simulate inserting into temp table which I know works. I am skipping it 
here for brevity:

-- Insert .MDF file names in table
INSERT INTO #datafiles_mdf EXEC (@cmd) 

-- Show the result set
SELECT * FROM #datafiles_mdf
*/
GO

SET NOCOUNT OFF
GO

The output of the FIRST set of code looks like this:

@cmd = master..xp_cmdshell ' dir /B "D:\Program Files\Microsoft SQL Server\MSSQL$MTB_2005\data\*.mdf" '

(11 row(s) affected)

output
-------------------
distmdl.mdf
master.mdf
model.mdf
msdbdata.mdf
MTB_CTS_DEV.mdf
MTB_CTS_Prod.mdf
MTB_CTS_ST.mdf
MTB_CTS_Test.mdf
MTB_CTS_UAT.mdf
tempdb.mdf
NULL

Now I try code that looks up the .MDF filepath,
assigns it to a variable, then proceeds.

The following does NOT work:


SET NOCOUNT ON
GO

DECLARE @path_db varchar(255) 
DECLARE @Cmd varchar(255) 

-- Delete Temp filed if it exists
If Object_ID('TempDB..#datafiles_mdf') Is Not Null
Begin
	Drop Table #datafiles_mdf
End

-- Get the Path where the .MDF files reside: 
SET @path_db = ( SELECT [filename] FROM master..sysfiles WHERE name = 'master' )

-- Remove the Path from the FilePath: 
SET @path_db = REPLACE(@path_db,'master.mdf','')

-- Examine the path as a check:
PRINT '@path_db = ' + CONVERT(nvarchar(255), @path_db)

Result:
@path_db = D:\Program Files\Microsoft SQL Server\MSSQL$MTB_2005\data\

Note: the Path string has a dollar ("$") sign in it which IS correct.

Now I try to combine rest of the command string. Note: I have tried numerous variations of this that have NOT worked including using "QUOTENAME", but I think I must not know how to
use it correctly:


SET @cmd = 	'master..xp_cmdshell ' + CHAR(39) + 
		' dir /B ' + CHAR(34) + @path_db + 
		CHAR(34) + CHAR(32) + CHAR(39)

PRINT ''
PRINT '@cmd = ' + CONVERT(nvarchar(255),@cmd)

Results:

@cmd = master..xp_cmdshell ' dir /B "D:\Program Files\Microsoft SQL Server\MSSQL$MTB_2005\data\

Needs to be:
@cmd = master..xp_cmdshell ' dir /B "D:\Program Files\Microsoft SQL Server\MSSQL$MTB_2005\data\*.mdf" '

The string "dir /B "D:\Program Files\Microsoft SQL Server\MSSQL$MTB_2005\data\*.mdf" runs properly in DOS.



-- Create the Temp table to hold the Names of the .MDF files
CREATE TABLE #datafiles_mdf 
(mdf varchar(255)) 

-- Insert .MDF file names in table
INSERT INTO #datafiles_mdf EXEC (@cmd) 

-- Show the result set
SELECT * FROM #datafiles_mdf

GO

SET NOCOUNT OFF
GO

Unfortunately, no matter what I try, I get errors
complaining about the string contained in the @cmd
variable.

The following is a typical error:

"Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string
' dir /B "D:\Program Files\Microsoft SQL Server\MSSQL$MTB_2005\data\"

Note: the *.mdf" substring is missing.
The "$" and the other characters (e.g. "\"., "/", etc.) must be causing
the errors. I think the "$" sign is prohibiting the concatenation
of the .MDF File Path with '*.mdf" '

Does anyont have any suggestions?

Thanks ahead of time!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-19 : 00:09:10

If you like to get all .mdf files in the instance, can just query master.sys.sysdatabases table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources