Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error 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 table name 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 &amp; 16)&lt;&gt;0 then 'clustered' else 'nonclustered' end
        + case when (status &amp; 1)&lt;&gt;0 then ', '
        + 'ignore duplicate keys' else '' end
        + case when (status &amp; 2)&lt;&gt;0 then ', '
        + 'unique' else '' end
        + case when (status &amp; 4)&lt;&gt;0 then ', '
        + 'ignore duplicate rows' else '' end
        + case when (status &amp; 64)&lt;&gt;0 then ', '
        + 'statistics' else case when (status &amp; 32)&lt;&gt;0 then ', '
        + 'hypothetical' else '' end end
        + case when (status &amp; 2048)&lt;&gt;0 then ', '
        + 'primary key' else '' end
        + case when (status &amp; 4096)&lt;&gt;0 then ', '
        + 'unique key' else '' end
        + case when (status &amp; 8388608)&lt;&gt;0 then ', '
        + 'auto create' else '' end
        + case when (status &amp; 16777216)&lt;&gt;0 then ', '
        + 'stats no recompute' else '' end),
        'index_name' = name
from    sysindexes 
where   (status &amp; 64) = 0
order by id


Which looked what i wanted. But there was still the same result of ambiguous 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.
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'