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,

asked 01 Apr '10, 23:32

Brad%20Wery's gravatar image

Brad Wery
382192126
accept rate: 0%

Comment Text Removed

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

(02 Apr '10, 08:57) Breck Carter

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.

(02 Apr '10, 18:26) Brad Wery

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"

permanent link

answered 02 Apr '10, 20:20

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

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

permanent link

answered 02 Apr '10, 10:14

Harry's gravatar image

Harry
1
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25

question asked: 01 Apr '10, 23:32

question was seen: 1,529 times

last updated: 02 Apr '10, 20:20