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>