Identify unattached File Size in SQL Server From a similar post on this forum, I have modified the below code as per comments and able to get the orphaned files in the drives. The code is :

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

if object_id('tempdb.dbo.#os_files') is not null
    drop table #os_files
create table #os_files([filename] varchar(2000))

insert into #os_files exec xp_cmdshell 'DIR D:\SQL04\*.mdf /b /s'
insert into #os_files exec xp_cmdshell 'DIR D:\SQL04\*.ldf /b /s'

delete from #os_files where filename is null

update #os_files set filename=rtrim(filename)

select 
    os.filename as orphaned_files
from 
    #os_files os
    left outer join master.sys.master_files db on rtrim(db.physical_name) = os.filename
where 
    db.database_id is null


EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO


Is it possible in any way to modify the above code and get the size of the files in the output apart from physical file names? 
Also is there any other way to modify the above code to search for files other than .mdf and .ldf files in the content volumes, where the database and log files reside. In our environment we have folders where mdf and ldf files are kept separately. Hence, the need.

The output that I'm expecting should be like this:

PHYSICAL_FILENAME      SIZE
        file1             s1
        file2             s2


Kindly look into this issue and give your suggestion.
Thanks in advance.
Basically if you manage the contents of the files you select from and include the file size, it should not be too hard to do.  (But there are issues.)  Here is a greatly edited script that may help you.

    -- Gather the full names of Database Files
SET NOCOUNT ON
create table #os_files(fullfilename varchar(2000));

insert into #os_files exec xp_cmdshell 'DIR D:\DATA\*.mdf /B /S'
insert into #os_files exec xp_cmdshell 'DIR D:\DATA\*.ndf /B /S'
insert into #os_files exec xp_cmdshell 'DIR F:\LOGS\*.ldf /B /S'

create table #os_files_dir(dirstring varchar(2000)); 

-- Gather Filename (without path) and Filesize without commas (/-C). 
-- Exclude system files (/A-S) and directories (/A-D)  
insert into #os_files_dir exec xp_cmdshell 'DIR D:\DATA\*.mdf /-C /TC /A-S /A-D /S';
insert into #os_files_dir exec xp_cmdshell 'DIR D:\DATA\*.ndf /-C /TC /A-S /A-D /S';
insert into #os_files_dir exec xp_cmdshell 'DIR F:\LOGS\*.ldf /-C /TC /A-S /A-D /S';

create table #os_files_dirclean(cleanstring varchar(2000)); 
-- Clean out non-file rows: Exclude NULL columns,
--  all the information rows that begin with a ' ' 
--  and handle a 'File Not Found' row, if it appears.
insert into #os_files_dirclean 
select * from #os_files_dir
WHERE SUBSTRING(dirstring,1,1) LIKE '[0-9]';

-- Select the database files that need to be cleaned up.
SELECT os.fullfilename AS PHYSICAL_FILENAME,  
       CAST(SUBSTRING(fd.cleanstring,21,18) AS BIGINT) AS SIZE
FROM #os_files os JOIN #os_files_dirclean fd
       ON os.fullfilename like '%' + SUBSTRING(fd.cleanstring,40,2000)
    LEFT OUTER JOIN master.sys.master_files db 
       ON RTRIM(db.physical_name) LIKE '%'+ RTRIM(os.fullfilename) 
WHERE db.database_id IS NULL

-- Clean up the temp tables
drop table #os_files;
drop table #os_files_dir;
drop table #os_files_dirclean;


Issues that make this a fragile solution:


In order to get the full path name, used the `DIR ... /B /S'
In order to get the file name and size, used the `DIR ... /-C /TC /A-S /A-D /S'
Since in step 2 the first column of a row with a file is the date and time of the file. Used this to filter out all rows that do not start with a numeric.
Since step 1 and step 2 return different name formants, this results in needing to join a full path name with a file name only.
A file name can appear more than once on a server, if it is in a different location. So there may be some ambiguity in the join if that case arises on one of your servers.


If you need other file types, you will need to include them also in loading the table. One SQL Server example is ".ndf" used when adding files to the database beyond the primary .mdf. I included this in the dataset.  

For a server I tested with I got the following.  Note that RLFJUNK is not really a database file, it is just named like one:

PHYSICAL_NAME                       SIZE
D:\DATA\Administration_Data.MDF     524288000
D:\DATA\EventHorizon_Data.MDF       629145600
D:\DATA\RLFJUNK.mdf                 19
F:\LOGS\Administration_log.LDF      107216896
F:\LOGS\EventHorizon_Log.LDF        2202009600


Future changes to the OS or the format of the DIR command could easily disrupt the script.  Still, hope it helps you for the time being.

This is more like something that you might want to find or develop a bit of CLR code to get the directory information without so much maneuvering.