Solved

SQL review all indexes

Posted on 2014-01-23
7
351 Views
Last Modified: 2014-01-27
I would like to add to the below query the name of the index?  

SELECT SCHEMA_NAME(o.schema_id) AS [schema]
,object_name(i.object_id ) AS [table]
,p.rows
,user_seeks
,user_scans    
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
ORDER BY rows desc
0
Comment
Question by:dastaub
[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
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39805392
>I would like to add to the below query the name of the index?  
Is that a statement or a question?
SELECT o.name as index_name 
FROM sys.indexes i 
   INNER JOIN sys.objects o ON i.object_id = o.object_id

Open in new window

0
 

Author Comment

by:dastaub
ID: 39805456
Is that a statement or a question?  = a question.
0
 

Author Comment

by:dastaub
ID: 39805461
SELECT o.name as index_name
FROM sys.indexes i
   INNER JOIN sys.objects o ON i.object_id = o.object_id

this query returns table names not index names.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 17

Expert Comment

by:dbaSQL
ID: 39805494
I'm not sure if I am reading your post properly.  Why wouldn't you just do this?
(It's your query, just re-structured for visibility.  I only added the third line, i.name -- for the name from sys.indexes.)

SELECT
      SCHEMA_NAME(o.schema_id) [schema],
      object_name(i.object_id ) [table],
      i.name,
      p.rows,
      user_seeks,
      user_scans,  
      user_lookups,
      user_updates,
      last_user_seek,
      last_user_scan,
      last_user_lookup
FROM
      sys.indexes i INNER JOIN sys.objects o
        ON i.object_id = o.object_id INNER JOIN sys.partitions p
          ON i.object_id = p.object_id
            AND i.index_id = p.index_id LEFT OUTER JOIN sys.dm_db_index_usage_stats ius
              ON i.object_id = ius.object_id
              AND i.index_id = ius.index_id
ORDER BY
      rows desc
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 total points
ID: 39811778
there you go

SELECT SCHEMA_NAME(o.schema_id) AS [schema]
,object_name(i.object_id ) AS [table]
,i.name  -- index name
,p.rows 
,user_seeks
,user_scans    
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
FROM sys.indexes i 
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
ORDER BY rows desc

Open in new window

0
 

Author Comment

by:dastaub
ID: 39812052
the above query supplies the data and answers the question, but it does repeat the data.  The same data appears more than once in the results.
0
 

Author Comment

by:dastaub
ID: 39812053
The below query is another option.  The data does not repeat.

SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS TableName,
i.name AS IdxName,
i.type_desc AS IdxType,
ius.user_seeks,
ius.user_scans,
ius.user_lookups,
ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
ON  ius.object_id = i.object_id
AND ius.index_id = i.index_id
AND database_id = DB_ID()--Current DB
INNER JOIN sys.tables t
ON t.object_id = i.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE
t.type = 'U'
AND t.is_ms_shipped = 0
ORDER by user_updates desc,QUOTENAME(t.name),i.name
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

689 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