Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to determine which tables have indexes

Posted on 1999-01-26
3
998 Views
Last Modified: 2011-10-03
I'm using Sybase SQL Server 10.0.2 on AIX
I have a sybase DB and I've used sp_tables to get a list of all the tables.

There are about 50 tables in it, so going through each one is a bit tiresome.

My questions are: How do I determine which tables have indexes in them, how do I tell what the name of those indexes are, and how do I tell what type of index they are (clustered or normal)?

I think it's by using the sp_statistics command, but then what is the exact syntax?  I'm running this through the ISQL program.
0
Comment
Question by:spenner
3 Comments
 
LVL 9

Expert Comment

by:david_levine
ID: 1098595
I'm not at work to create the correct syntax, and I can do that tomorrow if you need, but you can look at the sysobjects table for the majority of the information. Start poking around in there first to pick out the "user" tables (type = "U").

select
"Table" = sysobjects.name,"Index" = sysindexes.name
from sysobjects, sysindexes
where sysobjects.id = sysindexes.id and
sysobjects.type = "U"
order by sysobjects.name, sysindexes.name

to just list tables and indexes.

I know you want columns and I can get that tomorrow if you need, but this is a start and you might be able to figure it out from this.
0
 
LVL 10

Accepted Solution

by:
bret earned 50 total points
ID: 1098596


select object_name(id) as "table name", indid, name as "index name" from sysindexes where
indid > 0 and indid < 250


Rows with indid = 1 are clustered indexes, rows with higher
indids are nonclustered indexes.
0
 
LVL 2

Author Comment

by:spenner
ID: 1098597
David_Levine:
The SQL statement you gave did give me the indexes, but only those indexes for the system tables(ie. spt_values, spt_committab, spt_monitor, etc.)  However, Bret's answer listed all of the indexes, of both the system and user tables, as well as listing which ones were clustered, and which ones were standard.  While your comment was partially correct, Bret's answer was what I needed, so I am accepting Bret's answer.

Thanks to both of you
Spenner
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains the steps required to use the default Photos screensaver to display branding/corporate images
ConnectWise and their customers need to ensure critical alerts automatically reach the right person at the right time. MSP superheros efficiently respond to these alerts key is providing automatic, intelligent alerting that generates a complete audi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question