Fetching database file details for current database using sys table

Posted by Bandi under Sql Server category on | Points: 40 | Views : 408
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 ,
FROM sys.database_files
ORDER BY File_id;

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

Comments or Responses

Login to post response