SQL Server/T-SQL Tutorial/System Tables Views/sysindexes
returns space usage information
7> SELECT name = CONVERT(CHAR(30), o.name), rows, dpages, o.id, type
8> FROM sysindexes i JOIN sysobjects o ON o.id = i.id
9> WHERE o.id < 100 AND (indid = 0 OR indid = 1)
10> GO
1>
2>
select FROM sysindexes WHERE id = object_id("HasText")
3> CREATE TABLE HasText
4> (
5> Col1 char(3) NOT NULL,
6> Col2 text NOT NULL,
7> Col3 varchar(5) NULL,
8> Col4 varchar(20) NOT NULL,
9> Col5 smallint NULL
10> )
11> GO
1>
2> INSERT HasText VALUES
3> ("AAA", REPLICATE("X", 250), NULL, "ABC", 123)
4> GO
(1 rows affected)
1>
2> SELECT indid, first, name FROM sysindexes
3> WHERE id = object_id("HasText")
4> GO
indid first
name
------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------
--------------------------------------------------
0 0x7E0000000100
NULL
(1 rows affected)
1>
2>
3> drop table HasText;
4> GO
1>
table sysindexes contains a row for each index and a row for each table without a clustered index.
3>
4> select top 10 * from sysindexes;
5> GO
id status first
indid root
minlen keycnt groupid dpages reserved used rowcnt rowmodctr reserved3 reserved4 xmaxlen maxirow OrigFillFact
or StatVersion reserved2 FirstIAM
impid lockflags pgmodctr keys
name
statblob
maxlen rows
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------ ------ ------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------ ------ ------ ------- ----------- ----------- ----------- -------------------- ----------- --------- --------- ------- ------- ------------
-- ----------- ----------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------- ------ --------- ----------- -----------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------
-------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
4 18 0x0F0000000100
1 0x6C0000000100
34 2 1 7 9 9 697 1179600 0 0 37 19
0 0 0 0x930000000100
0 0 0 NULL
clust
NULL
8000 697
5 18 0x100000000100
1 0x100000000100
39 1 1 1 2 2 102 742 0 0 42 15
0 0 0 0xA70000000100
0 0 0 NULL
clust
NULL
8000 102
7 18 0x130000000100
1 0x780100000100
69 1 1 2 4 4 113 744 0 0 73 15
0 0 0 0x140000000100
0 0 0 NULL
clust
NULL
8000 113
8 0 0x200000000100
0 0x200000000100
786 0 1 1 2 2 2 4 0 0 789 0
0 0 0 0x0D0000000100
0 0 0 NULL
NULL
NULL
8000 2
13 18 0x110000000100
1 0x150000000100
41 2 1 7 15 9 697 4341 0 0 45 19
0 0 0 0x940000000100
0 0 0 NULL
clust
NULL
8000 697
15 18 0x120000000100
1 0x120000000100
37 1 1 1 2 2 102 742 0 0 41 15
0 0 0 0x6F0000000100
0 0 0 NULL
clust
NULL
8000 102
25 18 0x000000000000
1 0x000000000000
61 1 1 0 0 0 0 0 0 0 65 15
0 0 0 0x000000000000
0 0 0 NULL
clst
NULL
8000 0
26 18 0x8A0300000100
1 0x960000000100
21 3 1 1 2 2 113 744 0 0 24 24
0 0 0 0x950000000100
0 0 0 NULL
clust
NULL
8000 113
27 18 0xB00200000100
1 0x460100000100
29 1 1 1 6 6 15 1 0 0 908 15
0 0 0 0x450100000100
0 0 0 NULL
clst
NULL
8000 15
27 2 0xB20200000100
2 0x440100000100
5 2 1 1 2 2 15 1 0 0 281 267
0 0 0 0x430100000100
0 0 0 NULL
nc1
NULL
8000 15
(10 rows affected)
1>