Solved

SQL review all indexes

Posted on 2014-01-23
7
312 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQl query 19 12
SQL Server Reporting Services Service Start Timeout 4 15
Sql query 34 19
SQL Date Retrival 7 27
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.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

705 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

21 Experts available now in Live!

Get 1:1 Help Now