select
    logical_name
,   physical_name
,   [파일명.확장자]
,   substring(reverse(substring(reverse(physical_name), 1, charindex('\', reverse(physical_name))-1)), 1, charindex('.', [파일명.확장자])-1) [파일명]
,   substring(reverse(substring(reverse(physical_name), 1, charindex('\', reverse(physical_name))-1)), charindex('.', [파일명.확장자])+1, len([파일명.확장자])) [확장자]
from (
    SELECT 
        name logical_name
    ,   physical_name
    ,   reverse(substring(reverse(physical_name), 1, charindex('\', reverse(physical_name))-1)) [파일명.확장자]
    FROM sys.database_files WHERE type = 0 
) t