The following T-SQL script will extract all data/log files for the currently attached databases, and then scan all of those directories to find files that are not being used by SQL Server. This will quickly locate mdf, ldf or ndf files located in your data and log directories to free up space.

SET NOCOUNT ON;
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'func_SplitPath')
	DROP FUNCTION dbo.func_SplitPath
GO
IF object_id('tempdb..#tmp_sql_files') IS NOT NULL
BEGIN
   DROP TABLE #tmp_sql_files
END
GO
IF object_id('tempdb..#tmp_os_files') IS NOT NULL
BEGIN
   DROP TABLE #tmp_os_files
END
GO
CREATE FUNCTION dbo.func_SplitPath
(@filename varchar(2048), @part int)
RETURNS varchar(2048)
AS
BEGIN

	DECLARE @pos AS int
	
	SET @pos = CHARINDEX('\',REVERSE(@filename))

	IF (@pos > 0)
		BEGIN
			SET @pos = LEN(@filename) - @pos
			IF (@part = 1) -- Directory
				RETURN LEFT(@filename,@pos)
			ELSE
				RETURN RIGHT(@filename,LEN(@filename) - @pos - 1)
		END
	RETURN ''
END
GO

DECLARE cur_dbs CURSOR
READ_ONLY
FOR SELECT name FROM master.dbo.sysdatabases WHERE DATABASEPROPERTYEX(name,'Status') = 'ONLINE'

DECLARE @dbname sysname
OPEN cur_dbs

CREATE TABLE #tmp_sql_files (filename varchar(2048) NOT NULL, dir varchar(2048) NULL)

FETCH NEXT FROM cur_dbs INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		DECLARE @sql varchar(2048)

		SET @sql = 'SELECT RTRIM(filename),NULL FROM [' + @dbname + '].dbo.sysfiles'

		INSERT INTO #tmp_sql_files
		EXEC(@sql)
		
	END
	FETCH NEXT FROM cur_dbs INTO @dbname
END


CLOSE cur_dbs
DEALLOCATE cur_dbs

-- Split the files and directories portions up
UPDATE #tmp_sql_files
SET dir = dbo.func_SplitPath(filename,1)

UPDATE #tmp_sql_files
SET filename = dbo.func_SplitPath(filename,0)



--- Get files in these directories from the OS
DECLARE cur_files CURSOR
READ_ONLY
FOR SELECT DISTINCT dir FROM #tmp_sql_files

DECLARE @dir varchar(2048)
OPEN cur_files

CREATE TABLE #tmp_os_files (filename varchar(2048))

FETCH NEXT FROM cur_files INTO @dir
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		DECLARE @cmd varchar(255)
		SET @cmd = 'dir /B ' + @dir
		
		INSERT INTO #tmp_os_files
		EXEC master..xp_cmdshell @cmd

		UPDATE #tmp_os_files
		SET filename = @dir + '\' + filename
		WHERE filename IS NOT NULL AND CHARINDEX('\',filename) = 0
	END
	FETCH NEXT FROM cur_files INTO @dir
END

CLOSE cur_files
DEALLOCATE cur_files

DELETE FROM #tmp_os_files
WHERE filename IS NULL

ALTER TABLE #tmp_os_files ADD dir varchar(2048) NULL;
GO
-- Split the files and directories portions up
UPDATE #tmp_os_files
SET dir = dbo.func_SplitPath(filename,1)

UPDATE #tmp_os_files
SET filename = dbo.func_SplitPath(filename,0)


-- files in data/log directories that are not being used by sql server
SELECT o.dir, o.filename
FROM #tmp_os_files o
	LEFT OUTER JOIN #tmp_sql_files s ON
		o.filename = s.filename
WHERE s.filename IS NULL
	AND (o.filename LIKE '%.mdf'
		OR o.filename LIKE '%.ldf'
		OR o.filename LIKE '%.ndf')