Chapter 3 DBMS Reference Guide

SqlSysIndexQuery

SQL query used to list system indexes created by the database. These indexes are excluded during reverse engineering.

Example

In Sybase Adaptive Server Anywhere 6:

{OWNER, TABLE, INDEX}
select u.user_name, t.table_name, i.index_name 
from sys.sysindex i, sys.systable t, SYSUSERPERMS u
where "unique" = 'U' and t.table_id = i.table_id and u.user_id = t.creator [and u.user_name='%OWNER%'] [and t.table_name='%TABLE%']
union
select distinct u.user_name, t.table_name, t.table_name || '(primary key)'
from sys.systable t, SYSUSERPERMS u
where u.user_id = t.creator and t.primary_root <>0 [and u.user_name='%OWNER%'] [and t.table_name='%TABLE%']
union
select distinct u.user_name, t.table_name, f.role || '(foreign key)'
from sys.systable t, SYSUSERPERMS u, sys.sysforeignkey f
where u.user_id = t.creator and t.table_id = f.foreign_key_id [and u.user_name='%OWNER%'] [and t.table_name='%TABLE%']
union
select distinct u.user_name, t1.table_name, f.role || '(foreign key)'
from sys.systable t1, sys.systable t2, SYSUSERPERMS u, sys.sysforeignkey f
where u.user_id = t1.creator and t2.table_id = f.primary_table_id and t1.table_id=f.foreign_table_id [and u.user_name='%OWNER%'] [and t1.table_name='%TABLE%']

Copyright (C) 2005. Sybase Inc. All rights reserved.