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