Finding Indexes For A Table in Sybase
drop index sybase
sybase help center
sybase list columns in table
I am currently working in an environment where I am made double blind.
- I have to email any queries I need to run to a employee of the client
- That employee runs them in a SAS client, with an odbc connection to SyBase
I need to find out how to ascertain exactly what indexes exist on a specific table. I would use
sp_helpindex, but apparently it doesn't exist on their instance of SyBase.
We believe that it is SyBase 12, but again I can't be certain of anything.
Does anyone have SQL for... - Confirming exactly what version of SyBase we're working on? - The details of all indexes that exist for a given table?
To "Confirming exactly what version of SyBase we're working on?"
Why not use:
Sybase website is down (at least here), but it would be something like:
IQ SHOW INDEXSET INDEXES
IQ SHOW INDEXSET FOR indexset
tablename because every table has a indexset assigned with the same name.
If you have access to sybase website possibly you can go further :)
Finding Indexes For A Table in Sybase, To "Confirming exactly what version of SyBase we're working on?" Why not use: select @@version. Sybase website is down (at least here), but� */ exec sp_getmessage 17640, @msg output print @msg return (1) end /* ** Now check out each index, figure out it's type and keys and ** save the info in a temporary table that we'll print out at the end. */ create table #spindtab ( index_name varchar(255), index_keys varchar(1024), index_description varchar(68), index_max_rows_per_page smallint
Most Sybase products have a -v command line argument to tell the version with or without the engine running.
To find the indexes on any table enter the following, where
my_table is your table's name:
select i.name from sysindexes i, sysobjects o where o.name = 'my_table' and o.id = i.id
sp_helpindex, Reports information about the indexes created on a table. Syntax. sp_helpindex objname. Parameters. objname. is the name of a table in the current database. Indexes also improve performance dramatically when joining tables. When executing a query, SQL Anywhere chooses how to access each table. When the database server cannot find a suitable index, it resorts to scanning the table sequentially—a process that can take a long time.
You can use this query:
select i.name from sysindexes i, sysobjects o where o.name = 'table_name' and i.indid >=1 and o.id = i.id
Using system tables to identify the columns of a table index in Sybase, I came up with the following solution - thanks to the comment by Phil: SELECT distinct i.name as index_name, index_col(o.name, i.indid, c.colid) as name FROM� In other words, a nonclustered index has an extra level between the index structure and the data itself. Each of the up to 249 nonclustered indexes permitted on a table can provide access to the data in a different sorted order. Finding data using a clustered index is almost always faster than using a nonclustered index.
Find Indexes on multiple tables, not like image indexes
select name = o.name,iname = i.name from sysindexes i, sysobjects o where o.name in ('table1','table2','table3') and i.name not like 't%' and i.indid >=1 and o.id = i.id
Sybase: Find all tables in a database with a unique index , The following statement will return a list of all user tables in the current database which have a unique index: SELECT o.name FROM� Types of indexes. Adaptive Server provides two types of indexes: Clustered indexes, where the table data is physically stored in the order of the keys on the index: For allpages-locked tables, rows are stored in key order on pages, and pages are linked in key order.
For sybase version:
in Sybase version SAP IQ/16, you can get list of indexes with following (table name my_table is case sensitive:
select * from sys.sysindexes where tname = 'my_table';
Sybase Admin, To return only system tables, replace "'TABLE'" with "'SYSTEM TABLE'". Owners. This is a query to get all Sybase owners. select name from dbo.sysusers where� Find Indexes on a Table Using SP_HELPINDEX sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. This is the easiest method to find the indexes in a table. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.
Sybase Index Options, If not selected, a non-unique index is created where table rows can have duplicate Type: Sybase IQ uses HG or LF indexes to generate better or faster query� The default is nonunique. To create a nonunique clustered index on a table that contains duplicate rows, specify allow_dup_row or ignore_dup_row. See “Duplicate rows”. When you create a unique local index on range-, list-, and hash-partitioned tables, the index key list is a superset of the partition-key list. clustered
Sybase ASE Indexes (IEC Database - SAP CC 5.0), To opt-out of cookies, please select “More Information.” Sybase ASE Indexes indexes to speed up the retrieval of data stored in the database tables. Caution. In order to list all the tables and rows count, the following query can be used. select convert (varchar (30),o.name) AS table_name, row_count (db_id (), o.id) AS row_count from sysobjects o where type = 'U' order by table_name In order get a list of tables which has the table name like, use the following query.
size of a table and its indexes, how to get the size of the indexes from this output? 12/22/08 Bret Halford [ Sybase]. - show quoted To get the 10 largest tables, use a query something like.
- Thanks for this, though it appears that it's failing due to other system SPs being missing. I guess my client would prefer me to simply use the Force :)
- @Dems yep it's what I though it would happen ^^ But do you know which version is?
- Nearly 5 hours to find out, but I got it!
Sybase IQ/12.7.0/100114/P/ESD 9/Enterprise Linux64 - amd64,Opteron64,X86_64/2.4.21-27.0.1.ELsmp/64bit/2010-01-14 10:20:04
- @Dems check my update, it woulb be something like that I presume.