?
Solved

How to determine which tables have indexes

Posted on 1999-01-26
3
Medium Priority
?
1,028 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 200 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

We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
Hey fellow admins! This time, I have a little fairy tale for you. As many tales do, it starts boring and then gets pretty gory. I hope you like it. TL;DR: It is about an important security matter, you should read it if you run or administer Windows …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

800 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