Created
April 8, 2014 17:08
-
-
Save gregmac/10157265 to your computer and use it in GitHub Desktop.
sp_spaceused alternative
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Returns the same results of MS SQL Server sp_spaceused, but in a single result set | |
select | |
-- from first result set of 'exec sp_spacedused' | |
db_name() as [database_name] | |
,ltrim(str((convert (dec (15,2),sf.dbsize) + convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as [database_size] | |
,ltrim(str((case when sf.dbsize >= pages.reservedpages then | |
(convert (dec (15,2),sf.dbsize) - convert (dec (15,2),pages.reservedpages)) | |
* 8192 / 1048576 else 0 end),15,2) + ' MB') as [unallocated space] | |
-- from second result set of 'exec sp_spacedused' | |
,ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) + ' KB') as [reserved] | |
,ltrim(str(pages.pages * 8192 / 1024.,15,0) + ' KB') as data | |
,ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) + ' KB') as index_size | |
,ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) + ' KB') as unused | |
-- additional: | |
,ltrim(str((convert (dec (15,2),sf.dbsize)) * 8192 / 1048576,15,2) + ' MB') as dbsize | |
,ltrim(str((convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as logsize | |
FROM ( | |
select | |
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) as dbsize, | |
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) as logsize | |
from dbo.sysfiles | |
) sf, | |
( | |
select | |
sum(a.total_pages) as reservedpages, | |
sum(a.used_pages) as usedpages, | |
sum( | |
CASE | |
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" | |
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0 | |
When a.type <> 1 Then a.used_pages | |
When p.index_id < 2 Then a.data_pages | |
Else 0 | |
END | |
) as pages | |
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id | |
left join sys.internal_tables it on p.object_id = it.object_id | |
) pages |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment