/*===========================================================================================
File:				CheckDatabaseFileLocations.sql
Author:				Arthur Baan
Purpose:			Returns all database files for the instance, with a status if the file 
					is in the default file location.
Supported version:	This script works on SQL 2005 and higher.
------------------------------------------------------------------------------
Disclaimer:
This script does not make any changes to your server instance. But be careful running scripts
you found on the internet on an instance without knowing what the script does. If you run 
this script and your instance crashes do not blame me.
===========================================================================================*/
use master 
go

/*Declare two variables to store the default locations*/
declare @InstanceDefaultDataPath varchar(256)
declare @InstanceDefaultLogPath varchar(256)

/*we use a temp table to hold the databasefile information*/
if object_id('tempdb..#DatabaseFiles') is not null
begin
	drop table #DatabaseFiles
end

/*read the default data file location*/
exec xp_instance_regread 
	  N'HKEY_LOCAL_MACHINE'
	, N'Software\Microsoft\MSSQLServer\MSSQLServer'
	, N'DefaultData'
	, @InstanceDefaultDataPath output

/*read the default log file location*/
exec xp_instance_regread
  N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'DefaultLog'
, @InstanceDefaultLogPath output

/*add a backslash to the location*/
if right(@InstanceDefaultDataPath, 1) <> '\'
begin
	set @InstanceDefaultDataPath = @InstanceDefaultDataPath + '\'
end
if right(@InstanceDefaultLogPath, 1) <> '\'
begin
	set @InstanceDefaultLogPath = @InstanceDefaultLogPath+ '\'
end

/*get the data from sys.master_files and determine the correct default location*/
select 
  db_name(mf.[database_id]) [DatabaseName]
, mf.[physical_name] [PhysicalName]
, reverse(SUBSTRING(reverse(mf.[physical_name]),charindex('\',reverse(mf.[physical_name]),1),256)) [FilePath]
, [type_desc] [FileTypeDescription]
, [type] [FileType]
, case [type]
		when 0 then @InstanceDefaultDataPath
		when 1 then @InstanceDefaultLogPath
		else '' end [DefaultFileTypeLocation]
into #DatabaseFiles
from sys.master_files mf
where database_id > 4 /*we only check the user databases. Remove this line too also check system databases.*/

/*return the results. there are two results Result1 checks against the default location.
Result2 returns also checks for files located in subfolders of the default location*/
select
  [DatabaseName]
, [PhysicalName]
, [FilePath]
, case 
	when [FilePath] = [DefaultFileTypeLocation] and [FileType] in (0,1) then 'File in default location'
	when [FilePath] <> [DefaultFileTypeLocation] and [FileType] in (0,1) then 'File NOT in default location'
	when [FileType] not in (0,1) then 'File type ' + [FileTypeDescription]
  end [Result1]
, case 
	when charindex([DefaultFileTypeLocation],[FilePath]) > 0 and [FileType] in (0,1) then 'File in default location'
	when charindex([DefaultFileTypeLocation],[FilePath]) = 0 and [FileType] in (0,1) then 'File NOT in default location'
	when [FileType] not in (0,1) then 'File type ' + [FileTypeDescription]
  end [Result2]
 from #databaseFiles
