3

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.

1
  • 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? Jul 28, 2013 at 19:45

1 Answer 1

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'
2
  • 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, 2012 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, 2012 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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