SQL Server/T-SQL Tutorial/System Tables Views/sysindexes

Материал из SQL эксперт
Версия от 13:24, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

returns space usage information

   <source lang="sql">

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></source>


select FROM sysindexes WHERE id = object_id("HasText")

   <source lang="sql">

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></source>


table sysindexes contains a row for each index and a row for each table without a clustered index.

   <source lang="sql">

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></source>