Nearly...

You need to enable XP_cmdshell beforehand

I've also done a bit of tidying up

---- enable these jobs

---- show advanced options

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

---- enable xp_cmdshell

sp_configure 'xp_cmdshell', 1;

GO

RECONFIGURE;

GO

---- hide advanced options

sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

GO

-- based on http://www.sqlservercentral.com/Forums/Topic6166-5-1.aspx

-- create temporary table

create table #temp_mdf_files

(

full_filename varchar(200)

)

--populate the temp table with any MDF files found

insert #temp_mdf_files

exec xp_cmdshell 'dir c:\*.mdf /s/b'

insert #temp_mdf_files

exec xp_cmdshell 'dir d:\*.mdf /s/b'

insert #temp_mdf_files

exec xp_cmdshell 'dir e:\*.mdf /s/b'

--

select

-- exclude the subdirectory name

upper(reverse(substring(reverse(full_filename ), 1,charindex('\', reverse(full_filename ) )-1) )) As MDF_FileName,

full_filename

from #temp_mdf_files

where

--exclude rows which contain system messages or nulls

full_filename like '%\%'

--exclude system databases

and upper(reverse(substring(reverse(full_filename ), 1,charindex('\', reverse(full_filename ) )-1) ))

not in ('DISTMDL.MDF', 'MASTER.MDF', 'MODEL.MDF', 'MSDBDATA.MDF' , 'MSSQLSYSTEMRESOURCE.MDF', 'TEMPDB.MDF' )

-- MDF filename excluding the subdirectory name

and full_filename

not in (select Upper(FILEname) from sys.SYSdatabases)

order by MDF_FileName

-- Housekeeping

drop table #temp_mdf_files

-- disable these jobs

-- show advanced options

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

-- disable xp_cmdshell

sp_configure 'xp_cmdshell', 0;

GO

RECONFIGURE;

GO

-- hide advanced options

sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

GO