查看数据库表索引的使用情况

--查看数据库表索引的使用情况
SELECT  DB_NAME(database_id) AS N'数据库名' ,  --库名
        OBJECT_NAME(a.object_id) AS N'表名' ,
        b.name N'索引名称' ,
        user_seeks N'用户索引查找次数' ,
        user_scans N'用户索引扫描次数' ,
        last_user_seek N'最后查找时间' ,
        last_user_scan N'最后扫描时间' ,
        rows AS N'表中的行数'
FROM    sys.dm_db_index_usage_stats a
        JOIN sys.indexes b ON a.index_id = b.index_id
                              AND a.object_id = b.object_id
        JOIN sysindexes c ON c.id = b.object_id
WHERE   database_id = DB_ID('JRE')   ---改成要查看的数据库
        AND OBJECT_NAME(a.object_id) = 'T_Invoice'--改成要查看的表名
ORDER BY user_seeks ,
        user_scans ,
        OBJECT_NAME(a.object_id);

---

 查看指定表 Student 中的索引

EXEC sp_helpindex T_FPSJLR;

---

--创建非聚集索引
CREATE NONCLUSTERED INDEX IX_T_Attachment_Null3
ON T_Attachment(Null3)
WITH (DROP_EXISTING=OFF);

CREATE NONCLUSTERED INDEX idx_T_FPSJLR_FPHM_FPJE
ON [dbo].[T_FPSJLR] ([FPHM])
INCLUDE ([FPJE])
--删除索引
DROP INDEX IX_T_Attachment_Null3
ON T_Attachment;

 

转载于:https://www.cnblogs.com/zhyue93/p/sql_idx.html