SQL review all indexes

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
dastaubAsked:
Who is Participating?
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
dastaubAuthor Commented:
Is that a statement or a question?  = a question.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dastaubAuthor Commented:
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
 
dbaSQLCommented:
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
 
dastaubAuthor Commented:
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
 
dastaubAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.