May 21, 2012

sp_MSindexspace (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_MSindexspace(nvarchar @tablename
, nvarchar @index_name)

MetaData:

   
CREATE PROCEDURE sys.sp_MSindexspace
@tablename nvarchar(517), @index_name nvarchar(258) = NULL
AS
BEGIN

CREATE TABLE #IndexSizeTemp (
IndexID tinyint NOT NULL,
IndexName nvarchar(128) COLLATE database_default NOT NULL,
IndexSize int NOT NULL,
Comments nvarchar(28) COLLATE database_default NOT NULL
)

DECLARE @table_id int
DECLARE @index_id int
DECLARE @msg nvarchar(2000)
DECLARE @pagesize int
select @pagesize = v.low / 1024 from master..spt_values v where v.number=1 and v.type=N'E'

-- Make sure @tablename is local to the current database --

-- Make sure that @tablename and @index_name exist, we are checking table instead of UserTable --
SELECT @table_id = id
FROM dbo.sysobjects
WHERE (id = object_id(@tablename))
AND ((OBJECTPROPERTY(id, N'IsTable') = 1) OR (OBJECTPROPERTY(id, N'IsView') = 1))
IF (@table_id is NULL)
BEGIN
RAISERROR (15001, -1, -1, @tablename)
RETURN(1)
END
IF (@index_name is not NULL)
BEGIN
SELECT @index_id = indid
FROM dbo.sysindexes
WHERE (name = @index_name)
AND (id = object_id(@tablename))
IF (@index_id is NULL)
BEGIN
SELECT @msg = @tablename + N'.' + @index_name
RAISERROR (15001, -1, -1, @msg)
RETURN(1)
END
END
-- Ok, we're good to go --
IF (user_id() = 1)
CHECKPOINT
IF (@index_name is NULL)
BEGIN
INSERT INTO #IndexSizeTemp
SELECT indid, name, 0, N''
FROM dbo.sysindexes
WHERE (id = object_id(@tablename))
AND ((indid > 0) AND (indid < 255))
UPDATE #IndexSizeTemp
SET IndexSize = used * @pagesize,
Comments = N'(None)'
FROM dbo.sysindexes si, #IndexSizeTemp ist
WHERE (id = object_id(@tablename))
AND (indid > 1) AND (indid < 255)
AND (si.indid = ist.IndexID)
UPDATE #IndexSizeTemp
SET IndexSize = (used - dpages - isnull((SELECT sum(used)
FROM dbo.sysindexes
WHERE (indid > 1) AND (indid < 255)
AND (id = object_id(@tablename))), 0)) * @pagesize,
Comments = N'Size excludes actual data.'
FROM dbo.sysindexes si, #IndexSizeTemp ist
WHERE (id = object_id(@tablename))
AND (indid = 1)
AND (si.indid = ist.IndexID)
SELECT N'Index ID' = IndexID, N'Index Name' = IndexName, N'Size (KB)' = IndexSize, Comments
FROM #IndexSizeTemp
ORDER BY IndexID
DROP TABLE #IndexSizeTemp
END
ELSE
BEGIN
DECLARE @indid int
SELECT @indid = indid
FROM dbo.sysindexes
WHERE (id = object_id(@tablename))
AND (name = @index_name)
-- The non-clustered index case --
IF ((@indid > 1) AND (@indid < 255))
BEGIN
SELECT N'Size (KB)' = used * @pagesize
FROM dbo.sysindexes
WHERE (id = object_id(@tablename))
AND (name = @index_name)
RETURN(0)
END
-- The clustered index case --
IF (@indid = 1)
BEGIN
SELECT N'Size (KB)' =
(used - dpages - isnull((SELECT sum(used)
FROM dbo.sysindexes
WHERE (indid > 1) AND (indid < 255)
AND (id = object_id(@tablename))
AND (name = @index_name)), 0)) * @pagesize
FROM dbo.sysindexes
WHERE (id = object_id(@tablename))
AND (name = @index_name)
END
END
RETURN(0)
END

No comments:

Post a Comment

Total Pageviews