How to find orphan database files

Here I am going to provide sample query which help to identify database file are not using by SQL Server.

DECLARE @data_file_path VARCHAR(255)

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

-- create temporary table to capture file names from 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
	)

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

-- compare files found in the OS data file location to files associated with actual live databases.
-- WARNING: this does NOT take into consideration data files that have been detached. if you have detached
-- data files check with your DBA first before you clean up old orphaned data files.
SELECT 'path location' = @data_file_path
	,'orphaned data files' = subdirectory
FROM #folder
WHERE subdirectory LIKE '%df' -- compares ONLY to .mdf, .ndf, and .ldf
	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