March 6, 2006 at 1:58 am
How can I tell if an entry in the SysIndexes table refers to an INDEX or to STATISTICS?
March 6, 2006 at 9:54 am
The entry will conatin statistics for the index. Can you post an example of the data you are viewing in question? Are you looking at WU_% entries?
March 6, 2006 at 11:41 am
I am trying to make the distinction between entries created by CREATE INDEX vs those created by CREATE STATISTICS.
I know that the _WA_SYS entries are where SQL Server itself generates the statistics but there must be more to it than the name.
March 6, 2006 at 11:48 am
As simple as it seems, the name is, in fact, all there is to it.....other than a bit flag you would have to decode to use.....trust the name, it won't let you down....unless someone has been designing indexes and naming them like statistics....
March 6, 2006 at 12:28 pm
Do you happen to know what the bit flag is?
What I am trying to do is write a query that will find out when a foreign key constraint isn't supported by an index.
March 6, 2006 at 1:14 pm
I would query sysobjects, syscolumns, sysforeignkeys, sysindexes and sysindexkeys to get that info. Build from this:
Select
l_so.name
,l_sc.name
,l_sik.indid
,l_sik.keyno
From dbo.sysobjects l_so
Inner Join dbo.syscolumns l_sc
On l_so.id = l_sc.id
And l_so.id = Object_Id("sysobjects")
Left Outer Join dbo.sysindexkeys l_sik
On l_sc.colid = l_sik.colid
And l_sc.id = l_sik.id
Order By l_sc.colid
March 6, 2006 at 1:26 pm
Kory's method will get you what your trying to do, and is the way I would go about what you want.
Just for informational purposes, though, I will post the bitflag, it's decoding and meanings once I get home and can access my sql code library tonight...
March 6, 2006 at 1:48 pm
Thanks, the bit meaning is more robust than relying on naming conventions.
The query I was basing my work on was
select object_name(fkeyid)AS ReferencingTable,object_name(rkeyid) AS PrimaryTable,object_name(constid)AS FKName,c1.name AS ReferencingColumn,c2.name AS ReferencedColumn
from sysforeignkeys as fk
inner join syscolumns c1 on fk.fkeyid = c1.id and fk.fkey = c1.colid
inner join syscolumns c2 on fk.rkeyid = c2.id and fk.rkey = c2.colid
LEFT JOIN sysindexkeys s1 on
c1.id = s1.id
and c1.colid = s1.colid
and s1.keyno=1 -- Prefilters to check for referencing columns as the first column in an index.
where s1.id is null
March 6, 2006 at 1:50 pm
Hi Dave;
I think the value you are looking for is STATUS & 64=0 means index, STATUS & 64 <> 0 means statistics:
hope this helps: examples:
select top 100 object_name(I.id) as TableName,
I.Id as TableID,
I.indid as IndexId,
I.name as IndexName,
I.status,
INDEXPROPERTY (I.id,I.name,'IsUnique') as IsUnique,
INDEXPROPERTY (I.id,I.name,'IsClustered') as IsClustered,
INDEXPROPERTY (I.id,I.name,'IndexFillFactor') as IndexFillFactor
from sysindexes I
where I.indid > 0
and I.indid < 255
and (I.status & 64) = 0
select top 100 object_name(I.id) as TableName,
I.Id as TableID,
I.indid as IndexId,
I.name as IndexName,
I.status,
INDEXPROPERTY (I.id,I.name,'IsUnique') as IsUnique,
INDEXPROPERTY (I.id,I.name,'IsClustered') as IsClustered,
INDEXPROPERTY (I.id,I.name,'IndexFillFactor') as IndexFillFactor
from sysindexes I
where I.indid > 0
and I.indid < 255
and (I.status & 64)<> 0
Lowell
March 6, 2006 at 2:24 pm
there ya go.....and thanks, you saved me searching....
March 9, 2006 at 3:28 pm
Hi guys,
Quick question what does this mean, the "& 64"
(I.status & 64)
Thanks
March 10, 2006 at 12:51 pm
It is doing a bit comparison of a value.
If you think of an 8 bit number then
Bit 1 = 1
Bit 2 = 2
Bit 3 = 4
Bit 4 = 8
Bit 5 = 16
Bit 6 = 32
Bit 7 = 64
Bit 8 = 128
Therefore the maximum value that an 8 bit value can represent is 1+2+4+8+16+32+64+128 = 255
In your question (I.Status & 64) will return 64 providing the I.Status value is any value where Bit 7 is switched on
64 = Bit 7
65 = Bit 1 and Bit 7
66 = Bit 2 and Bit 7
67 = Bit1, 2 and 7 ...etc
If Bit 7 is not set it will return zero.
Click on the link in the first paragraph for more info on bit patterns.
March 10, 2006 at 2:13 pm
Thanks,
That could be a very useful piece of information.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply