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