How to find sybase table details ?

How do I find  indexes and foreign keys for a given table in Sybase  ASA?
vinb2Asked:
Who is Participating?
 
vinb2Author Commented:
sp_statistics table_name Returns a list of indexes on a single table.

Is there anything similar for foreign keys?
0
 
alpmoonCommented:
This should help for indexes:

SYSIDX system view
Each row in the SYSIDX system view defines a logical index in the database. The underlying system table for this view is ISYSIDX.

Column name  Column type  Description  
----------------  ---------------  ---------------
table_id  UNSIGNED INT  Uniquely identifies the table to which this index applies.
index_id  UNSIGNED INT  A unique number identifying the index within its table.
object_id UNSIGNED BIGINT The internal ID for the index, uniquely identifying it in the database.
phys_index_id UNSIGNED INT Identifies the underlying physical index used to implement the logical index. This value is NULL for indexes on temporary tables or remote tables. Otherwise, the value corresponds to the object_id of a physical index in the SYSPHYSIDX system view. See SYSPHYSIDX system view.  
dbspace_id  SMALLINT  The ID of the file in which the index is contained. This value corresponds to an entry in the SYSDBSPACE system view. See SYSDBSPACE system view.  
file_id SMALLINT DEPRECATED. This column is present in SYSVIEW, but not in the underlying system table ISYSIDX. The contents of this column is the same as dbspace_id and is provided for compatibility. Use dbspace_id instead.  
index_category TINYINT  The type of index. Values include:
1 - Primary key
2 - Foreign key
3 - Secondary index (includes unique constraints)
4 - Text indexes
 
"unique"  TINYINT  Indicates whether the index is a unique index (1), a non-unique index (4), or a unique constraint (2). A unique index prevents two rows in the indexed table from having the same values in the index columns.  
index_name  CHAR(128)  The name of the index.
not_enforced CHAR(1)  For system use only.
file_id SMALLINT For system use only.
0
 
alpmoonCommented:
This is for foreign keys:

SYSFKEY system view
Each row in the SYSFKEY system view describes a foreign key constraint in the system. The underlying system table for this view is ISYSFKEY.

Column name  Column type  Description
----------------  ---------------  ---------------
foreign_table_id  UNSIGNED INT  The table number of the foreign table.
foreign_index_id  UNSIGNED INT  The index number for the foreign key.
primary_table_id  UNSIGNED INT  The table number of the primary table.
primary_index_id  UNSIGNED INT  The index number of the primary key.
match_type TINYINT The matching type for the constraint. Matching types include:
0 - Use the default matching
1 - SIMPLE
2 - FULL
129 - SIMPLE UNIQUE
130 - FULL UNIQUE
For more information about match types, see the MATCH clause of the CREATE TABLE statement.
 
check_on_commit  CHAR(1)  Indicates whether INSERT and UPDATE statements should wait until the COMMIT to check if foreign keys are still valid.
nulls  CHAR(1)  Indicates whether the columns in the foreign key are allowed to contain the NULL value. Note that this setting is independent of the nulls setting in the columns contained in the foreign key.  
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This system procedure would do:

sp_helpindex ur_table_name
0
 
vinb2Author Commented:

rrjega17:  sp_helpindex is not supported. I'm working with ASA 7.0 version

alpmoon: I'm familiar with mysql but new to sybase so help me  with SYSFKEY view - which I could not find. The closest one I could find is SYSFKCOL and SYSFOREIGNKEYS view.

How can I get the table details on single table, as the database has hundreds of tables and I'm interested in finding details of only couple of them.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Just try

sp_help ur_table_name

This would list all columns, constraints, indexes present in a table..

PS: Not use ASE 7.0 but hope this works..
0
 
vinb2Author Commented:
Sorry - sp_help not supported. I guess I've to use System table view and join to get what I need.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try using

SELECT * FROM sysindexes
WHERE id = object_id('ur_table_name')
0
 
alpmoonCommented:
This is a very old version. SYSINDEX table or SYSINDEXES view give you all indexes.

You can find reference manual in archives:

http://manuals.sybase.com/onlinebooks/group-sasarc/awg0700e/dbrfen7



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.