-
Notifications
You must be signed in to change notification settings - Fork 8
/
SP_GETUSEDOBJECTS
332 lines (266 loc) · 14.1 KB
/
SP_GETUSEDOBJECTS
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
USE MASTER
GO
IF(OBJECT_ID('SP_GETUSEDOBJECTS')) IS NOT NULL
BEGIN
DROP PROC SP_GETUSEDOBJECTS
END
GO
CREATE PROCEDURE SP_GETUSEDOBJECTS
@SQLTEXT VARCHAR(MAX),
@EXECQUERY BIT = 0,
@GETSTATS BIT = 0,
@DBNAME VARCHAR(100) = '',
@HELP BIT = 0
AS
IF(@HELP <> 0)
BEGIN
PRINT
'
>>>>>>>>>> EXEMPLOS DE CHAMADA DA PROCEDURE: <<<<<<<<<<<<<<<<
1 - BUSCANDO PLANO EM MEMÓRIA
SP_GETUSEDOBJECTS
@HELP = 0,
@EXECQUERY = 0,
@GETSTATS = 0,
@SQLTEXT = "SELECT * FROM TABLE1 A INNER JOIN TABLE2 B ON A.ID = B.ID"
2 - EXECUTANDO A QUERY E CAPTURANDO PLANO ATUAL
SP_GETUSEDOBJECTS
@HELP = 0,
@EXECQUERY = 1,
@GETSTATS = 0,
@SQLTEXT = "SELECT * FROM TABLE1 A INNER JOIN TABLE2 B ON A.ID = B.ID"
3 - EXECUTANDO A QUERY E CAPTURANDO ESTATISTICAS UTILIZADAS
(WARNING) ESSA OPÇÃO PODE DEMORAR CONSIDERAVELMENTE COM BASE NOS DADOS EM CACHE
SP_GETUSEDOBJECTS
@HELP = 0,
@EXECQUERY = 1,
@GETSTATS = 1,
@SQLTEXT = "SELECT * FROM TABLE1 A INNER JOIN TABLE2 B ON A.ID = B.ID"
4 - INFORMAÇÕES ADICIONAIS
SP_GETUSEDOBJECTS
@HELP = 1,
@EXECQUERY = 0,
@GETSTATS = 0,
@SQLTEXT = "SELECT * FROM TABLE1 A INNER JOIN TABLE2 B ON A.ID = B.ID"
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SP_GETUSEDOBJECTS
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Nome Procedure: SP_GETUSEDOBJECTS
Projeto.......: SCRIPTS
Versão........: 1.0.0.0
---------------------------------------------------------------------------------------------------------------------------------------------
SQL Server versões homologadas: SQL Server 2016 e 2017.
SQL Server versões testadas: SQL Server 2008 e superiores.
---------------------------------------------------------------------------------------------------------------------------------------------
Id Autor Versão Data Descrição
---------------------------------------------------------------------------------------------------------------------------------------------
1 Reginaldo da Cruz Silva 1.0.0.0 26/07/2017 Criação da procedure.
2 Reginaldo da Cruz Silva 1.0.0.0 17/09/2018 Revisão de código.
Créditos:
@Caio Amante.
Blog: https://caiosamante.wordpress.com/
---------------------------------------------------------------------------------------------------------------------------------------------
Revisão:
Reginaldo da Cruz Silva - 27/07/2017 18:00
Dúvidas e sugestões:
Blog: https://blogdojamal.wordpress.com/
Email: Reginaldo.silva27@gmail.com
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
PARÂMETROS
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
PARÂMETRO DESCRIÇÃO
@HELP - EXIBE DETALHES DE VERSÃO, ATUALIZAÇÃO, PARAMETRIZAÇÕES, ASSIM COMO EXEMPLOS DE CHAMADA.
@SQLTEXT - TEXTO SQL QUE SERÁ PROCURADO NOS PLANOS DE EXECUÇÕES EM MEMÓRIA.
@EXECQUERY - EXECUTA A QUERY DO PARAMÊTRO @SQLTEXT.
@GETSTATS - (WARNING) ESSE PARAMÊTRO PODE DEGRADAR A PERFOMANCE DA EXECUÇÃO, ASSIM COMO A DO SERVIDOR DE SQL SERVER,
ESSE PARAMÊTRO TAMBÉM ATIVA O TRACE FLAG NÃO DOCUMENTADO (8666) PARA BUSCAR INFORMAÇÕES DAS ESTATISTICAS
PODENDO PREJUCAR A PERFORMANCE DO AMBIENTE DEPENDO DO TAMANHO DO CACHE DE DADOS(USE POR CONTA E RISCO).
@DBNAME - BANCO DE DADOS QUE SERÁ CONSULTADO, PARAMETRO RECEBE UM VALOR DEFAULT DO BANCO QUE ORIGINOU A CHAMADA.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DESCRIÇÃO DAS COLUNAS
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
COLUNA DESCRIÇÃO
DBNAME - NOME DA BASE DE DADOS EM USO
SCHEMANAME - NOME DO SCHEMA DA TABELA UTILIZADA NA EXECUÇÃO
TABLENAME - NOME DA TABELA UTILIZADA NA EXECUÇÃO
INDEXNAME - NOME DO INDICE UTILIZADO NA EXECUÇÃO
STAT_NAME - NOME DA ESTATISTICA UTILIZADA NO PLANO DE EXECUÇÃO
INDEX_TYPE - TIPO DO INDICE, VALORES PARA ESSA COLUNA: CLUSTERED, NONCLUSTERED E HEAP
COLUMNS - COLUNAS QUE PERTENCEM AO INDICE(ESTÃO EM TODOS OS NIVEIS DO INDICE)
INCLUDE_COLUMNS - COLUNAS QUE PERTENCEM AO INDICE(ESTÃO APENAS NO NIVEL FOLHA DO INDICE)
UPDATE_COMMAND - COMANDO PARA ATUALIZAR AS ESTATISCAS COM A OPÇÃO FULL SCAN
DROP_COMMAND - COMANDO PARA EXCLUIR O INDICE
CREATE COMMAND - COMANDO PARA CRIAR O INDICE
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'
RETURN
END
SET @DBNAME = DB_NAME();
DECLARE @SQLTEXT_AUX VARCHAR(MAX)
IF(@EXECQUERY = 1 AND @GETSTATS = 1)
BEGIN
SET @SQLTEXT_AUX = REPLACE(@SQLTEXT,'%','')
EXEC('DBCC TRACEON(8666,-1) ')
EXEC(@SQLTEXT_AUX)
END
ELSE
IF(@EXECQUERY = 1)
BEGIN
SET @SQLTEXT_AUX = REPLACE(@SQLTEXT,'%','')
EXEC(@SQLTEXT_AUX);
END
IF(OBJECT_ID('TEMPDB..##TABLE')) IS NOT NULL
BEGIN
DROP TABLE ##TABLE
END
CREATE TABLE ##TABLE (DBNAME VARCHAR(100),SCHEMANAME VARCHAR(100),TABLENAME VARCHAR(100), INDEXNAME VARCHAR(100))
DECLARE @XML VARCHAR(MAX),@DB VARCHAR(100),@SCHEMA VARCHAR(100), @TABLENAME VARCHAR(100), @INDEX VARCHAR(100)
DECLARE @CHARINI INT = 1, @CHARFIM INT = 0,@CHARAUX INT = 0
SELECT @XML = CAST(CP.query_plan AS VARCHAR(MAX))
FROM SYS.DM_EXEC_QUERY_STATS AS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)AS ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(PLAN_HANDLE) AS CP
WHERE ST.text = @SQLTEXT
WHILE @CHARINI <> 0
BEGIN
--DB
SET @CHARINI = CHARINDEX('Object Database="[',@XML,@CHARFIM)
IF(@CHARINI = 0)
BREAK;
IF(@CHARINI <> 0)
BEGIN
SET @CHARFIM = CHARINDEX(']',@XML,@CHARINI)
SELECT @DB = SUBSTRING(@XML,@CHARINI + 18,@CHARFIM - @CHARINI - 18)
END
--SCHEMA
SET @CHARINI = CHARINDEX('Schema="[',@XML,@CHARFIM)
IF(@CHARINI <> 0)
BEGIN
SET @CHARFIM = CHARINDEX(']',@XML,@CHARINI)
SELECT @SCHEMA = SUBSTRING(@XML,@CHARINI + 9,@CHARFIM - @CHARINI - 9)
END
--TABLE
SET @CHARINI = CHARINDEX('Table="[',@XML,@CHARFIM)
IF(@CHARINI <> 0)
BEGIN
SET @CHARFIM = CHARINDEX(']',@XML,@CHARINI)
SELECT @TABLENAME = SUBSTRING(@XML,@CHARINI + 8,@CHARFIM - @CHARINI - 8)
END
--INDEX
SET @CHARAUX =CHARINDEX('/>',@XML,@CHARFIM)
SET @CHARINI = CHARINDEX('Index="[',@XML,@CHARFIM)
IF(((@CHARINI - @CHARFIM) > (@CHARAUX - @CHARFIM)) OR (@CHARINI - @CHARFIM) < 0)
BEGIN
SET @CHARINI = CHARINDEX('IndexKind=',@XML,@CHARFIM)
SET @CHARFIM = CHARINDEX('"',@XML,@CHARINI)
SELECT @INDEX = SUBSTRING(@XML,@CHARINI + 11,@CHARFIM - @CHARINI - 6)
END
ELSE IF(@CHARINI <> 0)
BEGIN
SET @CHARFIM = CHARINDEX(']',@XML,@CHARINI)
SELECT @INDEX = SUBSTRING(@XML,@CHARINI + 8,@CHARFIM - @CHARINI - 8)
END
INSERT INTO ##TABLE VALUES(@DB,@SCHEMA,@TABLENAME,@INDEX)
END
DECLARE @RESULT TABLE(DBNAME VARCHAR(100),SCHEMANAME VARCHAR(100),TABLENAME VARCHAR(100), INDEXNAME VARCHAR(100), INDEX_TYPE VARCHAR(20), COLUMNS VARCHAR(1000),INCLUDE VARCHAR(1000), CREATE_COMAND VARCHAR(1000), DROP_COMAND VARCHAR(1000))
INSERT INTO @RESULT
EXEC('
USE '+@DBNAME+'
SELECT DISTINCT T.DBNAME,T.SCHEMANAME,T.TABLENAME,CASE WHEN T.INDEXNAME = ''Heap'' THEN '''' ELSE T.INDEXNAME END,ISNULL(CR.TIPO,UPPER(T.INDEXNAME)) AS INDEX_TYPE,ISNULL(CR.CAMPOS,'''') AS FIELDS,ISNULL(CR.INCLUDE,'''') INCLUDE,CASE WHEN T.INDEXNAME = ''Heap'' THEN ''ALTER TABLE ''+T.TABLENAME+'' REBUILD'' ELSE ISNULL(CR.COMANDO_CRIACAO,'''') END AS CREATE_COMMAND,ISNULL(CR.COMANDO_DELECAO,'''') AS DROP_COMMAND
FROM ##TABLE T
LEFT JOIN (
SELECT TABELA, INDICE, PRIMARY_KEY, INDICE_UNICO, UNIQUE_KEY, TIPO COLLATE SQL_Latin1_General_CP1_CI_AS TIPO,
SUBSTRING(CAMPOS,1,LEN(CAMPOS)-1)CAMPOS, SUBSTRING([INCLUDE],1,LEN([INCLUDE])-1)[INCLUDE],
CASE WHEN PRIMARY_KEY = 1 THEN ''ALTER TABLE ''+TABELA+'' ADD CONSTRAINT ''+INDICE+'' PRIMARY KEY ''+TIPO COLLATE SQL_Latin1_General_CP1_CI_AS +'' (''+SUBSTRING(CAMPOS,1,LEN(CAMPOS)-1)+'')''
WHEN UNIQUE_KEY = 1 THEN ''ALTER TABLE ''+TABELA+'' ADD CONSTRAINT ''+INDICE+'' UNIQUE ''+TIPO COLLATE SQL_Latin1_General_CP1_CI_AS +'' (''+SUBSTRING(CAMPOS,1,LEN(CAMPOS)-1)+'')''
WHEN PRIMARY_KEY = 0 AND UNIQUE_KEY = 0 AND INDICE_UNICO = 1 THEN ''CREATE UNIQUE ''+TIPO COLLATE SQL_Latin1_General_CP1_CI_AS +'' INDEX ''+INDICE+ '' ON ''+TABELA+'' (''+SUBSTRING(CAMPOS,1,LEN(CAMPOS)-1)+'')''+ CASE WHEN [INCLUDE] IS NOT NULL THEN '' INCLUDE (''+SUBSTRING([INCLUDE],1,LEN([INCLUDE])-1)+'')'' ELSE '''' END
WHEN PRIMARY_KEY = 0 AND UNIQUE_KEY = 0 AND INDICE_UNICO = 0 THEN ''CREATE ''+TIPO COLLATE SQL_Latin1_General_CP1_CI_AS +'' INDEX ''+INDICE+ '' ON ''+TABELA+'' (''+SUBSTRING(CAMPOS,1,LEN(CAMPOS)-1)+'')''+ CASE WHEN [INCLUDE] IS NOT NULL THEN '' INCLUDE (''+SUBSTRING([INCLUDE],1,LEN([INCLUDE])-1)+'')'' ELSE '''' END END AS COMANDO_CRIACAO,
CASE WHEN PRIMARY_KEY = 1 THEN ''ALTER TABLE ''+TABELA+'' DROP CONSTRAINT ''+INDICE
WHEN UNIQUE_KEY = 1 THEN ''ALTER TABLE ''+TABELA+'' DROP CONSTRAINT ''+INDICE
WHEN PRIMARY_KEY = 0 AND UNIQUE_KEY = 0 AND INDICE_UNICO = 1 THEN ''DROP INDEX ''+INDICE+ '' ON ''+TABELA
WHEN PRIMARY_KEY = 0 AND UNIQUE_KEY = 0 AND INDICE_UNICO = 0 THEN ''DROP INDEX ''+INDICE+ '' ON ''+TABELA END AS COMANDO_DELECAO
FROM(
SELECT TABELA,
INDICE,
MAX(CAST(IS_PRIMARY_KEY AS INT))PRIMARY_KEY,
MAX(CAST(IS_UNIQUE AS INT))INDICE_UNICO,
MAX(CAST(IS_UNIQUE_CONSTRAINT AS INT))UNIQUE_KEY,
MAX(TYPE_DESC) AS TIPO,
( SELECT CAMPO + '','' AS [text()]
FROM ( SELECT S.NAME AS TABELA, S6.NAME AS INDICE, S2.NAME AS CAMPO, S6.INDEX_COLUMN_ID AS ORDEM, S6.IS_INCLUDED_COLUMN AS INCLUD
FROM [DBO].[SYSOBJECTS] S
INNER JOIN [DBO].[SYSCOLUMNS] S2 ON S.ID = S2.ID
INNER JOIN [DBO].[SYSTYPES] S3 ON S2.XTYPE = S3.XTYPE AND S2.XUSERTYPE = S3.XUSERTYPE
INNER JOIN [SYS].[SCHEMAS] S4 ON S.[UID] = S4.[SCHEMA_ID]
LEFT JOIN [SYS].[IDENTITY_COLUMNS] S5 ON S2.ID = S5.[OBJECT_ID] AND S2.COLID = S5.COLUMN_ID
LEFT JOIN (SELECT S.[OBJECT_ID], S.NAME, S.TYPE_DESC, ISNULL(COLUMN_ID,0) AS COLUMN_ID, INDEX_COLUMN_ID, IS_INCLUDED_COLUMN
FROM [SYS].[INDEXES] S
INNER JOIN [SYS].[INDEX_COLUMNS] S2 ON S.[OBJECT_ID] = S2.[OBJECT_ID] AND S.INDEX_ID = S2.INDEX_ID
)S6 ON S6.[OBJECT_ID] = S.ID AND S6.COLUMN_ID = S2.COLID
WHERE S.XTYPE = ''U'' AND S4.NAME = ''DBO'' AND S6.COLUMN_ID <> 0 AND IS_INCLUDED_COLUMN = 0
)A
WHERE A.TABELA = B.TABELA AND A.INDICE = B.INDICE
ORDER BY TABELA,INDICE,ORDEM FOR XML PATH(''''))CAMPOS,
( SELECT CAMPO + '','' AS [text()]
FROM ( SELECT S.NAME AS TABELA, S6.NAME AS INDICE, S2.NAME AS CAMPO, S6.INDEX_COLUMN_ID AS ORDEM, S6.IS_INCLUDED_COLUMN AS INCLUD
FROM [DBO].[SYSOBJECTS] S
INNER JOIN [DBO].[SYSCOLUMNS] S2 ON S.ID = S2.ID
INNER JOIN [DBO].[SYSTYPES] S3 ON S2.XTYPE = S3.XTYPE AND S2.XUSERTYPE = S3.XUSERTYPE
INNER JOIN [SYS].[SCHEMAS] S4 ON S.[UID] = S4.[SCHEMA_ID]
LEFT JOIN [SYS].[IDENTITY_COLUMNS] S5 ON S2.ID = S5.[OBJECT_ID] AND S2.COLID = S5.COLUMN_ID
LEFT JOIN (SELECT S.[OBJECT_ID], S.NAME, S.TYPE_DESC, ISNULL(COLUMN_ID,0) AS COLUMN_ID, INDEX_COLUMN_ID, IS_INCLUDED_COLUMN
FROM [SYS].[INDEXES] S
INNER JOIN [SYS].[INDEX_COLUMNS] S2 ON S.[OBJECT_ID] = S2.[OBJECT_ID] AND S.INDEX_ID = S2.INDEX_ID
)S6 ON S6.[OBJECT_ID] = S.ID AND S6.COLUMN_ID = S2.COLID
WHERE S.XTYPE = ''U'' AND S4.NAME = ''DBO'' AND S6.COLUMN_ID <> 0 AND IS_INCLUDED_COLUMN <> 0
)A
WHERE A.TABELA = B.TABELA AND A.INDICE = B.INDICE
ORDER BY TABELA,INDICE,ORDEM FOR XML PATH(''''))[INCLUDE]
FROM (
SELECT S.NAME AS TABELA, S6.NAME AS INDICE, TYPE_DESC, IS_PRIMARY_KEY, IS_UNIQUE, IS_UNIQUE_CONSTRAINT, S2.NAME AS CAMPO, S6.INDEX_COLUMN_ID AS ORDEM, S6.IS_INCLUDED_COLUMN AS INCLUD
FROM [DBO].[SYSOBJECTS] S
INNER JOIN [DBO].[SYSCOLUMNS] S2 ON S.ID = S2.ID
INNER JOIN [DBO].[SYSTYPES] S3 ON S2.XTYPE = S3.XTYPE AND S2.XUSERTYPE = S3.XUSERTYPE
INNER JOIN [SYS].[SCHEMAS] S4 ON S.[UID] = S4.[SCHEMA_ID]
LEFT JOIN [SYS].[IDENTITY_COLUMNS] S5 ON S2.ID = S5.[OBJECT_ID] AND S2.COLID = S5.COLUMN_ID
LEFT JOIN (SELECT S.[OBJECT_ID], S.NAME, S.TYPE_DESC, IS_PRIMARY_KEY, IS_UNIQUE, IS_UNIQUE_CONSTRAINT, ISNULL(COLUMN_ID,0) AS COLUMN_ID, INDEX_COLUMN_ID, IS_INCLUDED_COLUMN
FROM [SYS].[INDEXES] S
INNER JOIN [SYS].[INDEX_COLUMNS] S2 ON S.[OBJECT_ID] = S2.[OBJECT_ID] AND S.INDEX_ID = S2.INDEX_ID
)S6 ON S6.[OBJECT_ID] = S.ID AND S6.COLUMN_ID = S2.COLID
WHERE S.XTYPE = ''U'' AND S4.NAME = ''DBO'' AND S6.COLUMN_ID <> 0
)B
GROUP BY TABELA, INDICE
)CONSULTA
) CR ON T.TABLENAME = CR.TABELA AND T.INDEXNAME= CR.INDICE
')
IF(@GETSTATS = 1)
BEGIN
DECLARE @TABLE_STATS TABLE (TB_NAME VARCHAR(100), STATS_NAME VARCHAR(1000))
DECLARE @TEXT VARCHAR(MAX)
SET @TEXT = '
USE '+@DBNAME+'
;WITH XMLNAMESPACES (''http://schemas.microsoft.com/sqlserver/2004/07/showplan'' as p)
SELECT OBJECT_NAME(S.OBJECT_ID) AS TABLE_NAME ,StatsUsed.XMLCol.value(''@FieldValue'',''NVarChar(500)'') AS StatsName
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) qt
CROSS APPLY query_plan.nodes(''//p:Field[@FieldName="wszStatName"]'') StatsUsed(XMLCol)
LEFT JOIN SYS.STATS S ON S.name = StatsUsed.XMLCol.value(''@FieldValue'',''NVarChar(500)'')
WHERE qt.text = '''+REPLACE(@SQLTEXT,'''','''''') +'''
'
INSERT INTO @TABLE_STATS
EXEC (@TEXT)
SELECT
DISTINCT T.DBNAME,T.SCHEMANAME,T.TABLENAME,T.INDEX_TYPE,T.INDEXNAME,ISNULL(TT.STATS_NAME,'') AS STATS_NAME,T.COLUMNS,T.INCLUDE,
ISNULL('UPDATE STATISTICS '+T.SCHEMANAME+'.'+T.TABLENAME+' '+TT.STATS_NAME+' WITH FULLSCAN;','') UPDATE_COMAND,
T.CREATE_COMAND,T.DROP_COMAND
FROM @RESULT T
LEFT JOIN @TABLE_STATS TT ON T.TABLENAME = TT.TB_NAME
ORDER BY T.TABLENAME
EXEC('DBCC TRACEOFF(8666,-1)')
END
ELSE
BEGIN
SELECT * FROM @RESULT
END
DROP TABLE ##TABLE