Fetching database file details for current database using sys table

To get the database details such as database name, virtaul path name, physical file path details, consumed space and growth for current database...

Here, sys.database_files gives us full details of physical path details of current database

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DatabaseName ,
        File_id ,
        Type_desc ,
        Name ,
        LEFT(Physical_Name, 1) AS Drive ,
        Physical_Name ,
        RIGHT(physical_name, 3) AS Ext ,
        Size ,
        Growth
FROM    sys.database_files
ORDER BY File_id;
GO

Sample OutPut:
Server DB_Name File_id Type_desc Name Drive Physical_Name Ext Size Growth

XXX\SQLSERVER	DB_New	1	ROWS	DB_New	C	C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_New.mdf	mdf	2127136	128
XXX\SQLSERVER	DB_New	2	LOG	DB_New_log	C	C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\DATA\DB_New_log.LDF	LDF	784504	10