How do I reverse engineer an index for a table from the system tables?

[Former Member]

How do I reverse engineer an index for a table from the system tables? I need to get all variations of the index syntax.

Note that I can not use a tool to get this information. I need to query the system tables.

Thanks,

[Breck_Carter]

What versions of SQL Anywhere are you interested in, what kinds of indexes do you want to know about, and how much do you want to know about them? These are important questions, because (for example) discovering the amount of disk space used by a foreign key in SQL Anywhere 5.5 is a vastly different task than in version 9, and it changed again for 10. And text indexes, they are a whole other story altogether. Plus, FWIW, materialized views can have indexes... you didn't want to hear that 🙂

[Former Member]

I need something for all version SQL Anywhere. I don't know how closely the syntax matches ASE but I would need something like the following:

CREATE NONCLUSTERED INDEX idx1 ON dbo.Test(col1)

CREATE CLUSTERED INDEX idx1 ON dbo.Test(col1)

CREATE UNIQUE CLUSTERED INDEX idx1 ON dbo.Test(col1)

Just the basics I guess.

[Breck_Carter · Accepted solution]

Not all of these tables exist in all versions; I recommend getting copies of 5.5, 6, 7, 8, 9, 10, 11 and 12, and settling down for a good read.

Also, some of the following are real tables in earlier versions, "compatibility views" in versions 10, 11 and 12. One table in particular, SYSATTRIBUTE, came into existence and then disappeared... with no compatibility view to help.

Here are the tables and columns you'll need; caveat emptor: I took a quick look at the Foxhound logic, not sure if the following is complete.

SYSUSERPERM.user_name

SYSTABLE.table_name

SYSINDEX.index_name

SYSINDEX."unique"

SYSPHYSIDX.max_key_distance will help determine FOR OLAP WORKLOAD in recent versions

SYSIDX is necessary to get you from SYSINDEX to SYSPHYSIDX

SYSATTRIBUTE.attribute_id, object_id, attribute_value will help determine CLUSTERED for some intermediate versions but not the latest

SYSTAB.clustered_index_id will help determine CLUSTERED for recent versions

SYSCOLUMN.column_name

SYSIXCOL.sequence

SYSIXCOL."order"

[Former Member]

I guess you can generate your index-SQL from this query?

select * 
from sys.sysidxcol 
        inner join sys.systabcol 
            inner join sys.systable 
                inner join sys.sysidx
where sys.systable.table_name = 'yourtable'
order by sys.sysidxcol.table_id, sys.sysidxcol.index_id, sys.sysidxcol.sequence;

I think this gives you all the information you need to build a INDEX statement. Should not be too hard.

Grtz Harry