|
Jude_Aj
Starting Member
22 Posts |
 Posted - 2009-06-10 : 08:54:02
|
| hi, The schema for sysindexes in SQL server 2005 is :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))*96 -- IS_INDEX (IND80_CREATE_STATS|IND80_HYPOTHETICAL) + (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 < 32000 AND has_access('CO', id) = 1 here i fail to understandOpenRowset(TABLE INDEXPROP, id, indid, rowset) p and FirstIAM = case when i.rowset > 0 then p.FirstIAM end,in the above query.can anybody help with this? |
|
sysindexes view schema