The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_spaceused(nvarchar @objname, varchar @updateusage)
MetaData:
create procedure sys.sp_spaceused -- - 2003/05/19 14:00 @objname nvarchar(776) = null, -- The object we want size on. @updateusage varchar(5) = false -- Param. for specifying that -- usage info. should be updated. as declare @id int -- The object id that takes up space ,@type character(2) -- The object type. ,@pages bigint -- Working variable for size calc. ,@dbname sysname ,@dbsize bigint ,@logsize bigint ,@reservedpages bigint ,@usedpages bigint ,@rowCount bigint -- -- Check to see if user wants usages updated. -- if @updateusage is not null begin select @updateusage=lower(@updateusage) if @updateusage not in ('true','false') begin raiserror(15143,-1,-1,@updateusage) return(1) end end -- -- Check to see that the objname is local. -- if @objname IS NOT NULL begin select @dbname = parsename(@objname, 3) if @dbname is not null and @dbname <> db_name() begin raiserror(15250,-1,-1) return (1) end if @dbname is null select @dbname = db_name() -- -- Try to find the object. -- SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname) -- Translate @id to internal-table for queue IF @type = 'SQ' SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 -- ITT_ServiceQueue -- -- Does the object exist? -- if @id is null begin raiserror(15009,-1,-1,@objname,@dbname) return (1) end -- Is it a table, view or queue? IF @type NOT IN ('U ','S ','V ','SQ','IT') begin raiserror(15234,-1,-1) return (1) end end -- -- Update usages if user specified to do so. -- if @updateusage = 'true' begin if @objname is null dbcc updateusage(0) with no_infomsgs else dbcc updateusage(0,@objname) with no_infomsgs print ' ' end set nocount on -- -- If @id is null, then we want summary data. -- if @id is null begin select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from dbo.sysfiles select @reservedpages = sum(a.total_pages), @usedpages = sum(a.used_pages), @pages = sum( CASE -- XML-Index and FT-Index and semantic index internal tables are not considered "data", but is part of "index_size" When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) Then 0 When a.type <> 1 and p.index_id < 2 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) 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 -- unallocated space could not be negative -- select database_name = db_name(), database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2) + ' MB'), 'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) * 8192 / 1048576 else 0 end),15,2) + ' MB') -- -- Now calculate the summary data. -- reserved: sum(reserved) where indid in (0, 1, 255) -- data: sum(data_pages) + sum(text_used) -- index: sum(used) where indid in (0, 1, 255) - data -- unused: sum(reserved) - sum(used) where indid in (0, 1, 255) -- select reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'), data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'), index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'), unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB') end -- -- We want a particular object. -- else begin -- -- Now calculate the summary data. * Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table * For non-clustered indices they are counted towards the index pages -- SELECT @reservedpages = SUM (reserved_page_count), @usedpages = SUM (used_page_count), @pages = SUM ( CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END ), @rowCount = SUM ( CASE WHEN (index_id < 2) THEN row_count ELSE 0 END ) FROM sys.dm_db_partition_stats WHERE object_id = @id; -- -- Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table -- IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222)) > 0 BEGIN -- -- Now calculate the summary data. Row counts in these internal tables don't -- contribute towards row count of original table. -- SELECT @reservedpages = @reservedpages + sum(reserved_page_count), @usedpages = @usedpages + sum(used_page_count) FROM sys.dm_db_partition_stats p, sys.internal_tables it WHERE it.parent_id = @id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) AND p.object_id = it.object_id; END SELECT name = OBJECT_NAME (@id), rows = convert (char(20), @rowCount), reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'), data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'), index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'), unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB') end return (0) -- sp_spaceused
No comments:
Post a Comment