Iam using below query to compare the physical location specified,but it is not showing the correct result and as the sys.files it is in diffrent

Is there any other method to compareas per the structure all my data folders to be in E:driveasnd log will be in f drivei want to know which db is not following the correct structure

print('Filename')exec('use['+@database_name+']insert #file_checkselect '''+@database_name+''', (select filename from sys.sysfiles where fileid =1 and filename not like ''H:\MSSQL\'+@database_name+'\DATA\'+@database_name+'_Data.mdf''),(select filename from sys.sysfiles where fileid =2 and filename not like ''I:\MSSQL\'+@database_name+'\LOG\'+@database_name+'_Log.ldf'')from sys.sysfiles;')

Is there any other method to find out,this is working for some databases some databases it is not

it is checking Sys.files table and the entire string is matching only it displays the correct result.

Is sys.master files can be used for this

Or please let me know the best method to find out the wrong structure.

thanks,my code is getting me the result in coupel of instances,onely few instances ,sys.files filename location is stored as case sensitive,that is why it is giving error messge,

is there any method to modify my code and get the result,i need to check all db,if it is not macthcing with the format specified,i should get the result of databases which is not correct as per the folder structure..

INSERT @t_TableNames_TempSELECT name FROM SYSDATABASES WHERE name not in ('pubs','northwind','tempdb')ORDER BY name--Getting row count from tableSELECT @iRowCount = COUNT(*) FROM @t_TableNames_TempWHILE @iRowCount > 0 BEGIN SELECT @database_name = table_name from @t_TableNames_Temp exec('use['+@database_name+'] insert #file_check select '''+@database_name+''', (select rtrim(filename) from sysfiles where fileid =1 and filename not like ''H:\MSSQL\'+@database_name+'\DATA\'+@database_name+'_Data.mdf''), (select rtrim(filename) from sysfiles where fileid =2 and filename not like ''I:\MSSQL\'+@database_name+'\LOG\'+@database_name+'_Log.ldf'') from sysfiles;') DELETE FROM @t_TableNames_Temp WHERE @database_name = table_name SELECT @iRowCount = @iRowCount - 1ENDSelect * from #file_check SET NOCOUNT OFF