?
Solved

Find Indexes

Posted on 2008-02-06
3
Medium Priority
?
130 Views
Last Modified: 2010-08-05
Is there a way to get a list of all indexes in all tables.  Also identify if they are non-clustered or clustered.  Using SQL 2005?
0
Comment
Question by:yanci1179
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 20836742
have you checked already out the following viesS:
INFORMATION_SCHEMA.INDEXES   --> ww.sqlservercentral.com/Forums/FindPost169988.aspx
sys.indexes   -> technet.microsoft.com/en-us/library/ms173760.aspx
0
 
LVL 18

Expert Comment

by:brejk
ID: 20839451
select
  quotename(schema_name(o.schema_id)) + '.' + quotename(o.name) as [object],
  i.name as [index],
  i.type_desc as [index type]
from sys.indexes i
inner join sys.objects o
on i.[object_id] = o.[object_id]
where i.name is not null
order by [object], i.index_id
0
 
LVL 18

Assisted Solution

by:brejk
brejk earned 800 total points
ID: 20839463
You may also add filter on type column in sys.objects catalog view if you want to see only indexes on user's tables:

select
  quotename(schema_name(o.schema_id)) + '.' + quotename(o.name) as [object],
  i.name as [index],
  i.type_desc as [index type]
from sys.indexes i
inner join sys.objects o
on i.[object_id] = o.[object_id]
where i.name is not null and o.type = 'U'
order by [object], i.index_id
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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