Solved

How to determine which tables have indexes

Posted on 1999-01-26
3
1,022 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
[X]
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
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Microsoft is moving in-place eDiscovery & hold from ECP to EOP console under Content Search in Search and Investigation Options.  In this post, I will be showing you how to export emails to a PST file using the Content Search Options.
This article outlines some of the reasons why an email message gets flagged as spam on a recipient's end.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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