How do I Display DB Properties?

This script returns the main properties for all data files and log files in the current database including logical and physical file names, size, used space, and file growth parameter.

 

1
2
3
4
5
6
7
8
9
SELECT fileid, sf.groupid, grp=LEFT(\[groupname\],20), lname=LEFT(\[name\],20), size_mb=\[size\]/128
,used_mb=FILEPROPERTY(\[name\], 'SpaceUsed')/128
,file_growth=case when (sf.status&0x100000) > 0 then str(growth)+' %'
else str(growth/128)+' mb' end
,max_mb=case when \[maxsize\]<0 then 'Unrestricted'
else str(\[maxsize\]/128) end
,phname=LEFT(filename,70)
FROM sysfiles sf LEFT OUTER JOIN sysfilegroups sfg ON sf.groupid=sfg.groupid
ORDER BY 1


 

 

Add Feedback