begin try
Select
(dense_rank() over (order by s.name,t.name))%2 as l1
, (dense_rank() over (order by s.name,t.name,i.name))%2 as l2
, s.name as [schema_name]
, t.name as [table_name]
, i.name as [index_name]
, i.type_desc
, case when iu.object_id is NULL then '' else convert(varchar(20),iu.object_id) end as object_id
, case when iu.index_id is NULL then '' else convert(varchar(20),iu.index_id) end as index_id
, case when iu.user_seeks is NULL then '' else convert(varchar(20),iu.user_seeks) end as seek_user
, case when iu.user_scans is NULL then '' else convert(varchar(20),iu.user_scans) end as scan_user
, case when iu.user_updates is NULL then '' else convert(varchar(20),iu.user_updates) end as update_user
, case when iu.last_user_seek is NULL then '' else iu.last_user_seek end as time_seek_user
, case when iu.last_user_scan is NULL then '' else iu.last_user_scan end as time_scan_user
, case when iu.last_user_lookup is NULL then '' else iu.last_user_lookup end as time_lookup_user
, case when iu.last_user_update is NULL then '' else iu.last_user_update end as time_update_user
, case when iu.system_seeks is NULL then '' else convert(varchar(20),iu.system_seeks) end as seek_system
, case when iu.system_scans is NULL then '' else convert(varchar(20),iu.system_scans) end scan_system
, case when iu.system_updates is NULL then '' else convert(varchar(20),iu.system_updates) end as update_system
, case when iu.last_system_seek is NULL then '' else iu.last_system_seek end as time_seek_system
, case when iu.last_system_scan is NULL then '' else iu.last_system_scan end as time_scan_system
, case when iu.last_system_lookup is NULL then '' else iu.last_system_lookup end as time_lookup_system
, case when iu.last_system_update is NULL then '' else iu.last_system_update end as time_update_system
from sys.dm_db_index_usage_stats iu
inner join sys.indexes i on ((iu.index_id = i.index_id) and (iu.object_id = i.object_id))
inner join sys.tables t on ( i.object_id = t.object_id )
inner join sys.schemas s on (s.schema_id = t.schema_id)
where iu.database_id = db_id() and i.type <> 0
order by s.name,t.name,i.name
end try
begin catch
select
-100 as l1
, 0 as l2
, ERROR_SEVERITY() as table_name
, ERROR_STATE() as index_name
, ERROR_MESSAGE() as type_desc
, ERROR_NUMBER() as object_id
, 0 as index_id
, 0 as seek_user
, 0 as scan_user
, 0 as update_user
, 0 as time_seek_user
, 0 as time_scan_user
, 0 as time_lookup_user
, 0 as time_update_user
, 0 as seek_system
, 0 as scan_system
, 0 as update_system
, 0 as time_seek_system
, 0 as time_scan_system
, 0 as time_lookup_system
, 0 as time_update_system
end catch