|
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" |
|
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 |
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 :)
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.