Where to find ignore dup key in system tables

Question

Post reply

Where to find ignore dup key in system tables

daniel.gueguen@netamine.fr

SSC Journeyman

Points: 97
More actions
December 17, 2011 at 8:08 pm

#98405

Hello,
Does someone know where to find 'ignore_dup_key' parameter for an index in SQL 2000 system tables ?
For SQL 2005 an above it's in sys.indexes, but I can't found this parameter in SQL 2000 and we can use it.
Thanks for your help
Daniel

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

salum SSCommitted Points: 1598 More actions · Answer 1

Try with using INDEXPROPERTY to get the index options.

SELECT INDEXPROPERTY(OBJECT_ID('authors'), 'indexname',

'IsUnique')

for more details.

http://msdn.microsoft.com/en-us/library/aa933195(v=sql.80).aspx

java[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Yes but ignore dup key is not one of the options for that function.

I've spent a few minutes looking all over the place and I can't find that info anywhere. The only place I didn't look is in the information_schema views... and maybe sp_help* objects.

daniel.gueguen@netamine.fr SSC Journeyman Points: 97 More actions · Answer 3

INDEXPROPERTY doesn't return information for ignore_dup_key and I have seen nothing in sp_help documentation.

But Enterprise Manager returns that information and I don't find how...

Daniel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

start profiler and open the property window. It must be hidden in a status column or something like that.

daniel.gueguen@netamine.fr SSC Journeyman Points: 97 More actions · Answer 5

daniel.gueguen@netamine.fr

SSC Journeyman

Points: 97

December 18, 2011 at 7:22 am

#1422900

already done... I have seen nothing about that property

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Close EM, start profile, open the GUI to see ignore_dup_key on.

Save the trace and send it over here (edit attachements).

daniel.gueguen@netamine.fr SSC Journeyman Points: 97 More actions · Answer 7

The only SQL statement I've seen is :

EXECUTE sp_MShelpindex N'[dbo].[Client]' , NULL, 1

I don't know what the column 'status' contains.

When ignore_dup_key is on 'status' contains 16810243 otherwise '16810242'

I suppose 'status' is a binary mask, but I have not found documentation on it.

Daniel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Go in master and open up that proc.

Copy the code out to QA and run one statement at a time untill you find the query that does the info. It's going to be binary operations (most likely with &).

It could look something like this CASE WHEN Status & <number> = <same number> then 1 END AS Ignore_dup_key

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Can't find it either on google.

This is the only thing I found that may help you out.

http://www.sqlservercentral.com/Forums/FindPost387744.aspx

I don't have access to my 2k5 machine so I can't open the trace.

daniel.gueguen@netamine.fr SSC Journeyman Points: 97 More actions · Answer 10

Ok, status comes from sysindexes.status :

select i.name, i.status, ...

from dbo.sysindexes i ...

and sysindexes.status is documented as 'Internal system-status information'

So, no information about format.

Daniel

daniel.gueguen@netamine.fr SSC Journeyman Points: 97 More actions · Answer 11

Thanks a lot for this link. It answer my question.

I have seen in the stored proc that status comes from susindexes.status and the code in your link shows how to read it :

case when (i.status & 1)<>0 then ', '+@IgnoreDuplicateKeys else @empty end

Thanks

Daniel

daniel.gueguen@netamine.fr SSC Journeyman Points: 97 More actions · Answer 12

Nice, it answers my question :

I have seen in the stored proc that status comes from sysindexes.status

and your link shows how to analyse it :

case when (i.status & 1)<>0 then ', '+@IgnoreDuplicateKeys else @empty end

Thanks a lot for your help

Daniel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Happy to help.

I'm sure I've seen those maps documented in the past. I just can't find them anymore. Granted it's almost 3 fulls versions back now. So many sites will be dropping that kind of info!

Where to find ignore dup key in system tables

Cookies on SQLServerCentral