续：有关SQL SERVER分布统计的问题(4)
来源：未知 责任编辑：责任编辑 发表时间:2014-02-02 17:50 点击:次
9: ColRows BigInt,
10: ColData_Pages BigInt)
11: GO
12: DECLARE @Tab TABLE (ROWID Int IDENTITY(1,1) PRIMARY KEY,
13: Table_Name VarChar(200),
14: Column_Name VarChar(200),
15: Stats_Name VarChar(200))
16:
17: DECLARE @i Int = 0,
18: @Table_Name VarChar(200) = '',
19: @Column_Name VarChar(200) = '',
20: @Stats_Name VarChar(200) = ''
21:
22: INSERT INTO @Tab (Table_Name, Column_Name, Stats_Name)
23: SELECT Schema_name(sys.objects.schema_id) + '.' + Object_Name(sys.stats.object_id) AS Table_Name,
24: sys.columns.name AS Column_Name,
25: sys.stats.Name AS Stats_Name
26: FROM sys.stats
27: INNER JOIN sys.stats_columns
28: ON stats.object_id = stats_columns.object_id
29: AND stats.stats_id = stats_columns.stats_id
30: INNER JOIN sys.columns
31: ON stats_columns.object_id = columns.object_id
32: AND stats_columns.column_id = columns.column_id
33: INNER JOIN sys.objects
34: ON stats.object_id = objects.object_id
35: LEFT OUTER JOIN sys.indexes
36: ON sys.stats.Name = sys.indexes.Name
37: WHERE sys.objects.type = 'U'
38: ORDER BY Table_Name
39:
40: SELECT TOP 1 @i = ROWID,
41: @Table_Name = Table_Name,
42: @Column_Name = Column_Name,
43: @Stats_Name = Stats_Name
44: FROM @Tab
45: WHERE ROWID > @I
46: WHILE @@RowCount > 0
47: BEGIN
48: --PRINT 'UPDATE STATISTICS "' + @Table_Name + '" "'+@Stats_Name+'" WITH FULLSCAN'
49: --EXEC ('UPDATE STATISTICS "' + @Table_Name + '" "'+@Stats_Name+'" WITH FULLSCAN')
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
