"Learning by example" opportunity here... spend some time looking things up on the web, as suggested in the code below.
And, like Frederico suggests, learn how to use "Object Aliases" and use them properly. They make code much easier to read and understand. They're also essential, in some areas. As a rule of thumb for me, I only use 2 part naming conventions. Using 3 part naming, like what would be required for columns from "sys" objects, has been deprecated as of SQL Server 2016. Search for "Three-part and four-part column references" at the following link (and you should search for such an article for each version of SQL Server that comes out and study it)...
The following code will also let you know why some "indexes" have no names, like Frederico said.
--===== Please snuggle up with the documentation!
-- Do a search for things like sys.inexes, sys.objects, the names of the functions, etc, etc.
-- Notice the use of the idx, obj, and usg "Object Aliases". THAT'S WHAT FREDERICO WAS TALKING ABOUT.
--===== Warning... sys.dm_db_index_usage_stats is totally cleared out on startup.
-- Warning... Indexes not used by users might still be evaluated by the optimizer. Don't just up and delete them.
-- Warning... Never delete indexes until you know for sure. Instead, disable them, which will NOT drop their definition.
-- Warning... It's your funeral if you don't read'n'heed the warnings above and below! ;)
WITH cteFindAll AS
(--==== This finds all indexes, even those not used since startup, which will NOT appear in sys.dm_db_index_usage_stats.
SELECT Schema_Name = SCHEMA_NAME(obj.schema_id) --I added this... you're going to need it.
,Table_Name = obj.name
,Index_Name = idx.name
,Index_Type = idx.type_desc
,User_Seeks = ISNULL(usg.user_seeks ,-1) --ISNULL() to find objects/identify not used since startup.
,User_Scans = ISNULL(usg.user_scans ,-1) --ISNULL() to find objects/identify not used since startup.
,User_Updates = ISNULL(usg.user_updates,-1) --ISNULL() to find objects/identify not used since startup.
,Is_Unique = idx.is_unique --Warning! I would NEVER disable/drop a unique keyed index unless you could absolutely prove it's not being used.
,Is_Disabled = idx.is_disabled
,Object_ID = idx.object_id
,Index_ID = idx.index_id
FROM sys.indexes idx
JOIN sys.objects obj ON obj.object_id = idx.object_id
LEFT JOIN sys.dm_db_index_usage_stats usg ON usg.OBJECT_ID = idx.OBJECT_ID --LEFT JOIN finds indexes not used since startup
AND usg.index_id = idx.index_id
WHERE idx.is_hypothetical = 0 --Warning! These indexes are special system "indexes". Don't worry about them!
AND obj.schema_id <> SCHEMA_ID('sys') --Comment this out to test... you might find some sys objects not used.
)--==== Report our findings even for indexes that haven't been used since startup.
WHERE User_seeks < 1 --Warning... Just because an index isn't used by users, it could be used by the system.
AND User_scans < 1 --We look for items < 1 so both 0 and -1 objects from the CTE will be found.
AND User_updates < 1 --Warning... Remember that "Reference" tables might NEVER be updated and only be used on occasion.
ORDER BY SIGN(Index_ID), Index_Type, Schema_Name, Index_Name
is pronounced "ree-bar
" and is a "Modenism
" for R
ow.First step towards the paradigm shift of writing Set Based code:________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.
Helpful Links:How to post code problemsHow to Post Performance ProblemsCreate a Tally Function (fnTally)