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 & 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 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.
