June 4, 2012

sp_MStablespace (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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_MStablespace(nvarchar @name
, int @id)

MetaData:

   
create procedure sys.sp_MStablespace
@name nvarchar(517), @id int = null
as
declare @rows int, @datasizeused int, @indexsizeused int, @pagesize int
declare @dbname nvarchar(128)
select @dbname = db_name()

if (@id is null)
select @id = id from dbo.sysobjects where id = object_id(@name) and (OBJECTPROPERTY(id, N'IsTable') = 1)
if (@id is null)
begin
RAISERROR (15009, -1, -1, @name, @dbname)
return 1
end

-- rows --
SELECT @rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @id

if (object_id('master.dbo.sp_MSSQLDMO90_version') is not null)
BEGIN

-- data --
SELECT @datasizeused =
SUM(CASE WHEN a.type <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = @id

-- index --
SELECT @indexsizeused =
sum(isnull(sidx.used,0)-isnull(sidx.dpages,0))
FROM dbo.sysindexes sidx
WHERE sidx.indid < 2 and sidx.id = @id
END
ELSE
BEGIN
-- data --
SELECT @datasizeused =
(SELECT sum(dpages)
FROM dbo.sysindexes
WHERE indid < 2 and id = @id)
+
(SELECT isnull(sum(used), 0)
FROM dbo.sysindexes
WHERE indid = 255 and id = @id)

-- Do not consider 2 < indid < 255 rows, those are nonclustered indices, and the space used by them are included by indid = 0(table) --
-- or indid = 1(clustered index) already. indid = 0(table) and = 1(clustered index) are mutual exclusive --
-- index --
SELECT @indexsizeused =
(SELECT sum(used)
FROM dbo.sysindexes
WHERE indid in (0, 1, 255) and id = @id)
- @datasizeused
END

-- Pagesize on this server (sysindexes stores size info in pages) --
select @pagesize = v.low / 1024 from master..spt_values v where v.number=1 and v.type=N'E'

select Rows = @rows, DataSpaceUsed = @datasizeused * @pagesize, IndexSpaceUsed = @indexsizeused * @pagesize

No comments:

Post a Comment

Total Pageviews