Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

we are using a Sybase SQL Anywhere 12 db.

In the db there are indices, which are unique, but shouldn't be unique.

Therefore I search for a quick way to list all tables with unique primary keys.

I tried

SELECT z.name FROM sysobjects z JOIN sysindexes ON (z.id = i.id) WHERE type = 'U'

The result was an error message: Anweisung konnte nicht ausgeführt werden. Tabellenname 'sysindexes' ist mehrdeutig SQLCODE=-852, ODBC 3-Status="42S13" Zeile 1, Spalte 1

Roughly translated: sysindex is ambiguous.

I found on internet the query:

select 'Table name' = object_name(id),'column_name' = index_col(object_name(id),indid,1),
'index_description' = convert(varchar(210), case when (status & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (status & 1)<>0 then ', '+'ignore duplicate keys' else '' end
+ case when (status & 2)<>0 then ', '+'unique' else '' end
+ case when (status & 4)<>0 then ', '+'ignore duplicate rows' else '' end
+ case when (status & 64)<>0 then ', '+'statistics' else case when (status & 32)<>0 then ', '+'hypothetical' else '' end end
+ case when (status & 2048)<>0 then ', '+'primary key' else '' end
+ case when (status & 4096)<>0 then ', '+'unique key' else '' end
+ case when (status & 8388608)<>0 then ', '+'auto create' else '' end
+ case when (status & 16777216)<>0 then ', '+'stats no recompute' else '' end),
'index_name' = name
from sysindexes where (status & 64) = 0
order by id

Which looked what i wanted. But there was still the same result of ambigious sysindexes.

What dows ambigious indexes mean in this context? Will/Can this cause any error in future?

As workaround I used sybase central (which by the way opens always on first monitor, not on the one where it was closed - ugly behaviour), and found that a item indices showed what i searched for.

But I still want to know how a programmatically solution looks like.

share|improve this question
    
What do you mean by "shouldn't be unique"? Do you mean that the data is overconstrained relative to the business rules of the subject matter? –  Walter Mitty Jul 28 '13 at 19:45

1 Answer 1

There are two system views called sysindexes - one owned by SYS and one owned by DBO. You likely want the the one owned by SYS:

SELECT z.name FROM sys.sysobjects z JOIN sys.sysindexes ON (z.id = i.id) WHERE type = 'U'
share|improve this answer
    
Hi, some problems: there seems to be no sys.sysobjects, and sys.sysindexes does not contain ID. The sql now looks like (and seems to work) –  Offler Dec 3 '12 at 7:23
    
Select z.name, i.name, 'index_description' = convert(varchar(210), //cases like above// FROM sysobjects z JOIN dbo.sysindexes i ON (z.id = i.id) WHERE type = 'U' –  Offler Dec 3 '12 at 7:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.