SQL Server/T-SQL Tutorial/System Tables Views/sysindexes — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 13:24, 26 мая 2010
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>