Solved

SQL review all indexes

Posted on 2014-01-23
7
344 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 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