-
Notifications
You must be signed in to change notification settings - Fork 95
/
Find overlapped statistics + DROP statement.sql
63 lines (57 loc) · 1.57 KB
/
Find overlapped statistics + DROP statement.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
USE [master]
GO
-- This script search for all overlapped statistics and prepare DROP statement
IF OBJECT_ID('tempdb..#autostats') IS NOT NULL
DROP TABLE #autostats
CREATE TABLE #autostats
(
[DataBase] NVARCHAR(128),
[Schema] NVARCHAR(128),
[Table] NVARCHAR(128),
[Column] SYSNAME,
[StatisticName] NVARCHAR(128),
[Overlapped by Index] NVARCHAR(128),
[ScriptToDropStitistic] NVARCHAR(200)
)
INSERT INTO #autostats
EXEC sp_MSforeachdb 'USE [?]
SELECT
DB_NAME(),
OBJECT_SCHEMA_NAME(S.[object_id]),
OBJECT_NAME(S.[object_id]),
C.[name],
A.[name],
S.[name],
''DROP STATISTICS ['' + OBJECT_SCHEMA_NAME(S.[object_id]) + ''].['' + OBJECT_NAME(S.[object_id]) + ''].[''+ A.[name] + '']''
FROM
sys.stats AS S
INNER JOIN sys.stats_columns AS SC ON S.[object_id] = SC.[object_id]
AND S.stats_id = SC.stats_id
INNER JOIN
(
SELECT
S.[object_id],
S.[stats_id],
S.[name],
SC.[column_id]
FROM
sys.stats S
INNER JOIN sys.stats_columns AS SC ON S.[object_id] = SC.[object_id]
AND S.stats_id = SC.stats_id
WHERE
S.auto_created = 1
AND SC.stats_column_id = 1
) A
ON SC.[object_id] = A.[object_id]
AND SC.column_id = A.column_id
INNER JOIN sys.columns AS C ON S.[object_id] = C.[object_id]
AND SC.column_id = C.column_id
WHERE
S.auto_created = 0
AND SC.stats_column_id = 1
AND SC.stats_id != A.stats_id
AND OBJECTPROPERTY(S.[object_id], ''IsMsShipped'') = 0 '
SELECT
*
FROM
#autostats