Get Used indexes from SQL Server

use this Stored Procedure to get Used Indexes from a Database on SQL Server:


Create proc P_GetUsedIndexes (@Databasename as varchar(50))

As

DECLARE @cmdstr varchar(100)

SELECT DATABASE_ID,
DB_NAME(DATABASE_ID) AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
INDEX_NAME = (SELECT NAME
FROM SYS.INDEXES A
WHERE A.OBJECT_ID = B.OBJECT_ID
AND A.INDEX_ID = B.INDEX_ID),
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS B
INNER JOIN SYS.OBJECTS C
ON B.OBJECT_ID = C.OBJECT_ID
WHERE --DATABASE_ID = DB_ID(DB_NAME()) AND
DATABASE_ID = (select [dbid] from master.dbo.sysdatabases(nolock) where name=lower(@DatabaseName)) AND
C.TYPE <> 'S'