SqlServer 之 檢視表空間

一、用到系統視圖 sys.sysindexes

　　該視圖定義如下：

CREATE VIEW sys.sysindexes AS
    SELECT id,
        status = convert(int,
            case indid when 1 then 16 else 0 end    -- (is_clustered * 16)
            + (status & 8)/4        -- IS_IND_UNIQUE (is_unique * 2)
            + (status & 4)/4        -- IS_IND_DPKEYS (ignore_dup_key * 1)
            + (status & 32)*64    -- IS_IND_PRIMARY (is_primary_key * 2048)
            + (status & 64)*64    -- IS_IND_UNIQUE_CO (is_unique_constraint * 4096)
            + (status & 16)*16    -- IS_IND_PADINDEX (is_padded * 256)
            + (status & 256)/8        -- IS_IND_ITWINDEX (is_hypothetical * 32)
            + (1-(status & 1))*64    -- IS_INDEX
            + (status & 8192)*1024    -- IS_STATS_AUTO_CRT (auto_created * 0x800000)
            + (status & 16384)*1024),    -- IS_STATS_NORECOMP (no_recompute * 0x1000000)
        first = case when i.rowset > 0 then p.first end,
        indid = convert(smallint, indid),
        root = case when i.rowset > 0 then p.root end,
        minlen = convert(smallint, case when status & 1 = 0 then 0 else indexproperty(id, name, 'minlen') end),
        keycnt = convert(smallint, indexproperty(id, name, 'keycnt80')),
        groupid = convert(smallint, case when dataspace < 32768 then dataspace end),
        dpages = convert(int, case when status & 1 = 0 then 0 else p.data_pages end),
        reserved = convert(int, case when status & 1 = 0 then 0 else p.total_pages end),
        used = convert(int, case when status & 1 = 0 then 0 else p.used_pages end),
        rowcnt = convert(bigint, case when status & 1 = 0 then 0 else p.rows end),
        rowmodctr = convert(int, indexproperty(id, name, 'rowmodcnt80')),
        reserved3 = convert(tinyint, 0),
        reserved4 = convert(tinyint, 0),
        xmaxlen = convert(smallint, case when status & 1 = 0 then 0 else indexproperty(id, name, 'maxlen') end),
        p.maxirow,
        OrigFillFactor = convert(tinyint, (status & 1)*fillfact),    -- fill_factor
        StatVersion = convert(tinyint, 0),
        reserved2 = convert(int, 0),
        FirstIAM = case when i.rowset > 0 then p.FirstIAM end,
        impid = convert(smallint, 0),
        lockflags = convert(smallint, (status & 512)/512 + (status & 1024)/512),    -- no_row_locks + no_page_locks*2
        pgmodctr = convert(int, 0),
        keys = convert(varbinary(1088), null),
        name = name,
        statblob = convert (image, null),
        maxlen = convert(int, 8000),
        rows = convert(int,  case when status & 1 = 0 then 0 else 0x7FFFFFFF & p.rows end)
    FROM sys.sysidxstats i OUTER APPLY OpenRowset(TABLE INDEXPROP, id, indid, rowset) p
    WHERE indid < 256000
        AND has_access('CO', id) = 1
GO      

　　各列定義如下：

列名	資料類型	描述
id	int	表 ID（如果 indid= 0 或 255）。否則為索引所屬表的 ID。
status		内部系統狀态資訊。
first	binary(6)	指向第一頁或根頁的指針。
indid	smallint	索引 ID： 1 = 聚集索引 > 1 = 非聚集 255 = 具有 text或 image 資料的表條目
root		如果 indid >= 1 和 < 255，root 是指向根頁的指針。如果 indid = 0 或 indid = 255，root 是指向最後一頁的指針。
minlen		最小行大小。
keycnt		鍵的數目。
groupid		在其上建立對象的檔案組 ID。
dpages		如果 indid = 0 或 indid = 1，dpages 是已用資料頁的計數。如果 indid = 255，其設定為 0。否則是已用索引頁的計數。
reserved		如果 indid = 0 或 indid = 1，reserved 是配置設定給所有索引和表資料的頁計數。如果 indid = 255，reserved 是配置設定給 text 或 image 資料的頁計數。否則是配置設定給索引的頁計數。
used		如果 indid = 0 或 indid = 1，used 是用于所有索引和表資料的總頁數。如果 indid = 255，used 是用于 text 或 image 資料的頁計數。否則是用于索引的頁計數。
rowcnt	bigint	基于 indid = 0 和 indid = 1 的資料級行計數。如果 indid = 255，rowcnt 設定為 0。
rowmodctr		對自上次更新表的統計後插入、删除或更新行的總數進行計數。
xmaxlen		最大行大小。
maxirow		最大非葉索引行大小。
OrigFillFactor	tinyint	建立索引時使用的起始填充因子值。不保留該值；然而，如果需要重新建立索引但記不住當初使用的填充因子，則該值可能很有幫助。
reserved1		保留。
reserved2
FirstIAM
impid		保留。索引實作标志。
lockflags		用于限制經過考慮的索引鎖粒度。例如，對于本質上是隻讀的查找表，可以将其設定為僅進行表級鎖定以使鎖定成本減到最小。
pgmodctr
keys	varbinary(816)	組成索引鍵的列 ID 清單。
name	sysname	表名（如果 indid= 0 或 255）。否則為索引的名稱。
statblob	image	統計 BLOB。
maxlen
rows		基于 indid = 0 和 indid = 1的資料級行數，該值對于 indid >1 重複。如果 indid = 255，rows 設定為 0。提供該列是為了向後相容。

SELECT OBJECT_NAME(id) tName,id,status,first,indid,root,minlen,keycnt,groupid,dpages,reserved,used,rowcnt,rowmodctr,
    reserved3,reserved4,xmaxlen,maxirow,OrigFillFactor,StatVersion,reserved2,FirstIAM,impid,lockflags,pgmodctr,keys,
    name,statblob,maxlen,rows
FROM sysindexes 
WHERE indid=1
ORDER BY reserved DESC      

IF NOT EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE tablespaceinfo --建立結果存儲表
(nameinfo varchar(50) ,
 rowsinfo int , reserved varchar(20) ,
 datainfo varchar(20) ,
 index_size varchar(20) ,
 unused varchar(20) )

delete from tablespaceinfo --清空資料表
 declare @tablename varchar(255) --表名稱
 declare @cmdsql varchar(500)
 DECLARE Info_cursor CURSOR FOR

SELECT o.name
FROM dbo.sysobjects o 
WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1 AND o.name NOT LIKE N'#%%' 
ORDER BY o.name

OPEN Info_cursor
 FETCH NEXT FROM Info_cursor
 INTO @tablename
 WHILE @@FETCH_STATUS = 0
 BEGIN
  if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  execute sp_executesql
  N'insert into tablespaceinfo exec sp_spaceused @tbname',
  N'@tbname varchar(255)',
  @tbname = @tablename
 FETCH NEXT FROM Info_cursor
 INTO @tablename
 END
 CLOSE Info_cursor
 DEALLOCATE Info_cursor
 GO

 --itlearner注：顯示資料庫資訊
 sp_spaceused @updateusage = 'TRUE'

--itlearner注：顯示表資訊
SELECT * FROM tablespaceinfo
ORDER BY cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc