记录几个 sql server 的 SQL

获取MS SQL SERVER 碎片化状况

declare @DB_ID int
  ,@OBJECT_ID int

set @DB_ID = DB_ID('MurooShien')
set @OBJECT_ID = OBJECT_ID('M001MsGetID')

select *
from sys.dm_db_index_physical_stats(@DB_ID, @Object_ID, null, null, 'DETAILED') as A
join sys.objects as B on A.object_id = B.object_id

索引rebuild
ALTER INDEX 索引名称 ON 库.表 REBUILD ;

rebuild所有的所有索引
ALTER INDEX ALL ON 库.表 REBUILD 

参考
https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017
https://techblog.zozo.com/entry/sqlserver-index-reorganize-vs-rebuild

获取表的数据行数
select rows from sysindexes where id = object_id('表名') and indid in (0,1)

获取索引信息
SELECT C.NAME AS TABLE_NAME,A.NAME AS INDEX_NAME,D.NAME AS INDEX_COLUMN_NAME
FROM SYS.INDEXES A   
JOIN SYS.INDEX_COLUMNS B ON (A.object_id = B.object_id AND A.index_id = B.index_id)   
JOIN SYS.TABLES C ON (A.object_id = C.object_id)   
JOIN SYS.COLUMNS D ON (A.object_id = D.object_id   AND B.column_id = D.column_id)

获取表的名称
select name from SYS.TABLES