Solved

SQL review all indexes

Posted on 2014-01-23
7
329 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
7 Comments
 
LVL 65

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Creating Records Where There Are None2 - The Sequel 6 26
Can > be used for a Text field 6 35
Tsql query 6 19
SQL view 2 26
In this article I will describe the Detach & Attach 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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

813 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

11 Experts available now in Live!

Get 1:1 Help Now