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

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