Solved

How to determine which tables have indexes

Posted on 1999-01-26
3
966 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
In this article, I will show you HOW TO: Suppress Configuration Issues and Warnings Alert displayed in Summary status for ESXi 6.5 after enabling SSH or ESXi Shell.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now