How to find orphan database files

In this post, I will share a sample SQL query that helps identify database files that are not in use by SQL Server.

DECLARE @data_file_path VARCHAR(255)

SET @data_file_path = 'D:\\data\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\'

-- Creating a temporary table to store file names from the directory.
IF object_id('tempdb..#folder') IS NOT NULL
    DROP TABLE #folder

CREATE TABLE #folder (
    cid INT identity(1, 1) PRIMARY KEY CLUSTERED,
    subdirectory VARCHAR(255),
    depth INT,
    isfile INT
)

-- Populating the temporary table with file names using xp_dirtree.
INSERT INTO #folder (
    [subdirectory],
    [depth],
    [isfile]
)
EXEC master..xp_dirtree @data_file_path, 1, 1

-- Comparing files found in the OS data file location to files linked to live databases.
-- WARNING: This does not account for detached data files. If you have detached data files, consult your DBA before cleaning up orphaned files.
SELECT 'path location' = @data_file_path,
       'orphaned data files' = subdirectory
FROM #folder
WHERE subdirectory LIKE '%df' -- Only compares .mdf, .ndf, and .ldf files
  AND subdirectory NOT IN (
        SELECT right(smf.physical_name, charindex('\\', reverse('\\' + smf.physical_name)) - 1)
        FROM sys.master_files smf
        JOIN sys.databases sd ON smf.database_id = sd.database_id
    )
ORDER BY subdirectory ASC