[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to determine which tables have indexes

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
As much as Microsoft wants to kill off PST file support, just as they tried to do with public folders, there are still times when it is useful or downright necessary to export Exchange mailboxes to PST files. Thankfully, it is still possible to e…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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