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.
asked 01 Apr '10, 23:32
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.
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
answered 02 Apr '10, 20:20
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.
answered 02 Apr '10, 10:14