Using SQL Server 2000 I have been looking at ways to accurately determine the number of pages used by a database. I was interested to see that the FILEPROPERTY function has a spaceused parameter which I used thus:
select sum(convert(dec(15),FILEPROPERTY(name,'spaceused')))
from sysfiles
where (status & 64 = 0)
BTW I am not interested in the transaction log thus the mask on the status column. This method tells me the total number of pages used across all the files used by the database.
When I look at the sp_spaceused procedure I see that there is essentially 1 query that tells me the number of pages reserved in the database. I used this query thus:
select sum(convert(dec(15),reserved)) from sysindexes where indid in (0,1,255)
The figure that I get from each of these queries is different. Now I know this info is coming from 2 different tables but I am interested in an explanation from anyone that knows what determines each of these figures and, therefore, why they are different. FYI on the test DB I was using, the first query was giving me 4680 and the second 4664.