添加索引更新统计信息

chi8708 · Jan 28, 2022 · 9bf38a8 · 9bf38a8
1 parent 4d4cb5c
commit 9bf38a8
Show file tree

Hide file tree

Showing 2 changed files with 44 additions and 0 deletions.
diff --git a/常用sql/更新表统计信息.sql b/常用sql/更新表统计信息.sql
@@ -0,0 +1,24 @@
+--先重建索引后再更新统计信息
+USE Test
+
+Go 
+
+DECLARE @DBCCString NVARCHAR(1000)
+DECLARE @TableName VARCHAR(100)
+DECLARE Cur_Index CURSOR FOR  SELECT Name AS TblName FROM sysobjects  WHERE xType='U' ORDER BY TblName
+FOR READ ONLY
+OPEN Cur_Index
+FETCH NEXT FROM Cur_Index INTO @TableName
+WHILE @@FETCH_STATUS=0
+BEGIN
+   SET @DBCCString = 'update STATISTICS [' +@TableName +']'
+   print @DBCCString
+   EXEC SP_EXECUTESQL  @DBCCString
+   PRINT convert(char(20),getdate(),120)+ '更新表' + @TableName +'的统计信息........OK!'
+   waitfor delay'00:00:02'
+   FETCH NEXT FROM Cur_Index INTO @TableName
+END
+CLOSE Cur_Index
+DEALLOCATE Cur_Index
+PRINT '操作完成！'
+
diff --git a/常用sql/重建表的索引.sql b/常用sql/重建表的索引.sql
@@ -0,0 +1,20 @@
+---先重建索引再更新统计信息
+Use Test
+Go 
+
+DECLARE @DBCCString NVARCHAR(1000)
+DECLARE @TableName VARCHAR(100)
+DECLARE Cur_Index CURSOR FOR  SELECT Name AS TblName FROM sysobjects  WHERE xType='U' ORDER BY TblName
+FOR READ ONLY
+OPEN Cur_Index
+FETCH NEXT FROM Cur_Index INTO @TableName
+WHILE @@FETCH_STATUS=0
+BEGIN
+   SET @DBCCString = 'DBCC DBREINDEX(@TblName,'''',90)WITH NO_INFOMSGS'
+   EXEC SP_EXECUTESQL  @DBCCString,N'@TblName VARCHAR(100)', @TableName
+   PRINT convert(char(20),getdate(),120)+ '重建表' + @TableName +'的索引........OK!'
+   FETCH NEXT FROM Cur_Index INTO @TableName
+END
+CLOSE Cur_Index
+DEALLOCATE Cur_Index
+PRINT '操作完成！'