vinb2
asked on
How to find sybase table details ?
How do I find indexes and foreign keys for a given table in Sybase ASA?
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.
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.
This system procedure would do:
sp_helpindex ur_table_name
sp_helpindex ur_table_name
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..
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..
ASKER
Sorry - sp_help not supported. I guess I've to use System table view and join to get what I need.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.