Shortcuts
SSMS > Options > Query Shorcuts such as:
sp_Indexess 0, standard analysis
sp_Indexess 1, advanced analysis (fragmentation analysis)
Call
Standard : this returns the structure of index like: key fields, included fields, type of index,
filter and few warnings like settings, is clustered index, has calculated fields, XML fields, etc.:
EXEC sp_Indexess 0, 'ABCC'
Advanced analysis with fragmentation and errors/warnings:
EXEC sp_Indexess 1, 'ABCC'
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS(SELECT * FROM sys.objects WHERE name = 'sp_Indexess')
BEGIN
EXEC('CREATE PROCEDURE sp_Indexess AS SELECT 1/0')
END
GO
ALTER PROCEDURE sp_Indexess @Analysis TINYINT = 0, @Obj SYSNAME -- 0 Standard, 1 Simple
WITH EXECUTE AS CALLER
AS
BEGIN
/*
FREEDOME License:
1. Not allowed: Modification
2. Warranty, Liability: Missing. Permitted: Commercial use, Distribution and Private use
*/
/*
Some basic details for warnings:
IsDisabled
IsHypothetical Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics.
Refc: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql
IsIgnoredInOptimization Most likely because of different session SET settings when this index was created
Refc: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16#required-set-options-for-filtered-indexes
HasFilter If exists it returns the definiton of index filter. Much likely will be unused (or used very rarely) because of this filter or because of settings.
Refc: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16#required-set-options-for-filtered-indexes
IsPadded ON. If OFF for some scenarious will be ignored.
Refc: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16#required-set-options-for-filtered-indexes
HasClusteredIndex If 0 then this table is heap structure. Every table sould have a clustered index.
Refc: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190639
IsUniqueClusteredIndex A clustered index that's not unique, SQL Server will add a hidden 4-byte column called UNIQUIFIER
Refc: https://michaeljswart.com/2024/08/large-uniquifier-values/
FKeysMultiColums This multi column unique index has FKs. And some fields are NULLable.
Refc: https://voluntarydba.com/2012/09/12/fun-with-multi-column-foreign-keys-and-nullable-columns/
WithCalculatedCol Current index has calculated columns. Some seetings are specific for such cases.
Refc: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16#required-set-options-for-filtered-indexes
*/
-- Standard settings
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
-- Declare
DECLARE @ObjId INT
DECLARE @SelectedTable SYSNAME
DECLARE @SelectedSchema SYSNAME
DECLARE @SelectedDb SYSNAME
DECLARE @CurrObj SYSNAME
DECLARE @CurrSchema SYSNAME
DECLARE @FullCurrObj SYSNAME
SELECT @SelectedTable = NULLIF(PARSENAME(@Obj, 1), '')
SELECT @SelectedSchema = NULLIF(PARSENAME(@Obj, 2), '')
SELECT @SelectedDb = NULLIF(PARSENAME(@Obj, 3), '')
IF @SelectedDb IS NOT NULL
BEGIN
RAISERROR('Unsupported format', 16, 1)
RETURN
END
IF @SelectedSchema IS NULL
BEGIN
DECLARE @rowct INT
SELECT @SelectedSchema = sch.name
FROM sys.objects obc JOIN sys.schemas sch ON obc.schema_id = sch.schema_id
WHERE obc.name COLLATE DATABASE_DEFAULT = PARSENAME(@Obj, 1) COLLATE DATABASE_DEFAULT
SELECT @rowct = @@ROWCOUNT
IF @rowct = 0
BEGIN
RAISERROR('Selected element is not a table/view', 16, 1)
RETURN
END
ELSE IF @rowct = 1
BEGIN
PRINT 'It''s Ok'
END
ELSE
BEGIN
RAISERROR('Please use this format sp_Indexess 0, ''schema.table''', 16, 1)
RETURN
END
END
SELECT @CurrObj = QUOTENAME(@SelectedTable)
SELECT @CurrSchema = QUOTENAME(@SelectedSchema)
SELECT @FullCurrObj = @CurrSchema + '.' + @CurrObj
SELECT @ObjId = OBJECT_ID(@FullCurrObj)
-- Body
SELECT ObjectName, IndexName, IndexId, RCCount, TypeDesc, KColumns, NColumns, WhereFilter, IsDisabledHypothetical,
IsPrimaryKey, IsUniqueIndex, IsUniqueConstraint, PhilFactor, AllowPageLocks, AllowRowLocks
FROM (SELECT QUOTENAME(sch.name) + '.' + QUOTENAME(objc.name) ObjectName,
QUOTENAME(ix.name) IndexName,
ix.index_id IndexId,
rcc.RCCount,
CASE
WHEN ix.type_desc = 'SPATIAL' THEN ix.type_desc + ' ' + spatil.spatial_index_type_desc
WHEN ix.type_desc = 'XML' THEN xmli.xml_index_type_description + ISNULL('_' + xmli.secondary_type_desc, '')
ELSE ix.type_desc
END TypeDesc,
KColumns,
NColumns,
ix.filter_definition WhereFilter,
ISNULL(NULLIF(CASE WHEN ix.is_disabled=1 THEN 'D' ELSE '' END + CASE WHEN ix.is_hypothetical=1 THEN 'H' ELSE NULL END, ''), '0') IsDisabledHypothetical,
ix.is_primary_key IsPrimaryKey,
ix.is_unique IsUniqueIndex,
ISNULL(QUOTENAME(kcois.name), ix.is_unique_constraint) IsUniqueConstraint,
ix.fill_factor PhilFactor,
ix.allow_page_locks AllowPageLocks,
ix.allow_row_locks AllowRowLocks,
/*(
SELECT
ix.is_padded,
is_ignored_in_optimization,
ix.compression_delay,
suppress_dup_key_messages,
auto_created,
optimize_for_sequential_key,
spatil.tessellation_scheme,
spatil.filter_definition spatial_filter,
spatial_bounding = '(xmin=' + STR(bounding_box_xmin, 28, 4) + ', ymin=' + STR(bounding_box_ymin, 38, 4) + ', xmax=' + STR(bounding_box_ymin, 38, 4) + ', ymax=' + STR(bounding_box_ymax, 38, 4) + ')' ,
level_1_grid_desc, level_2_grid_desc, level_3_grid_desc, level_4_grid_desc,
cells_per_object,
(
SELECT si.name, si.path
FROM sys.selective_xml_index_paths si
WHERE si.object_id = xmli.object_id
AND si.index_id = xmli.index_id
ORDER BY si.path_id
FOR XML RAW, TYPE
) SIDPaths,
hsh.bucket_count
FOR XML RAW, TYPE
) Others,*/
frang.AvgFragmentation,
frang.AvgPageSpaceUsed,
(
SELECT
IsDisabled = NULLIF(ix.is_disabled, 0),
IsHypothetical = NULLIF(ix.is_hypothetical, 0),
IsIgnoredInOptimization = NULLIF(is_ignored_in_optimization, 0),
HasFilter = CASE WHEN ix.has_filter=1 THEN ix.filter_definition END,
IsPadded = NULLIF(ix.is_padded, 0),
NonDefaultFillFactor = CASE WHEN ix.fill_factor = 0 OR ix.fill_factor = 100 THEN NULL ELSE 1 END,
HasClusteredIndex = CASE WHEN ix.type_desc = 'HEAP' THEN 0 ELSE NULL END,
AllowPageLocks = NULLIF(ix.allow_page_locks, 1), -- NCI
AllowRowLocks = NULLIF(ix.allow_row_locks, 1), -- NCI
HasFilegroups = CASE WHEN ix.data_space_id > 1 THEN 1 ELSE NULL END,
HasPartitions = NULLIF(NULLIF((SELECT COUNT(*) FROM sys.partitions ps WHERE ps.object_id = ix.object_id AND ps.index_id = ix.index_id), 1), 0),
IsUniqueClusteredIndex = CASE WHEN ix.type_desc = 'CLUSTERED'THEN NULLIF(ix.is_unique, 1) ELSE NULL END,
KeysIsMultiField= CASE WHEN ix.is_unique = 1 AND mc.IsKeyMultiColumn = 1 THEN 1 ELSE NULL END,
FKeysMultiField =
CASE WHEN ix.is_unique = 1 AND mc.IsKeyMultiColumn = 1 AND EXISTS(SELECT * FROM sys.foreign_keys fk WHERE fk.key_index_id = ix.index_id AND fk.referenced_object_id = ix.object_id)
THEN STUFF((SELECT ',' + QUOTENAME(fk.name) FROM sys.foreign_keys fk WHERE fk.key_index_id = ix.index_id AND fk.referenced_object_id = ix.object_id FOR XML PATH(N''), TYPE).value('.', N'NVARCHAR(MAX)'), 1, 1, '')
ELSE NULL
END,
HasCalculatedField= CASE WHEN EXISTS(SELECT * FROM sys.computed_columns cc JOIN sys.index_columns icc ON cc.object_id = icc.object_id AND cc.column_id = icc.column_id WHERE icc.object_id = ix.object_id AND icc.index_id = ix.index_id) THEN 1 END,
HasXmlField = CASE WHEN EXISTS(SELECT * FROM sys.all_columns cc JOIN sys.index_columns icc ON cc.object_id = icc.object_id AND cc.column_id = icc.column_id WHERE icc.object_id = ix.object_id AND icc.index_id = ix.index_id AND cc.system_type_id = 241/*XML*/) THEN 1 END,
PartitionCompression = (
SELECT number = ps.partition_number, ps.data_compression_desc, xml_compression_desc
FROM sys.partitions ps
OUTER APPLY (
SELECT NULL xml_compression, NULL xml_compression_desc
) odlv
WHERE ps.object_id = ix.object_id AND ps.index_id = ix.index_id
AND (ps.data_compression > 0 OR xml_compression > 0)
ORDER BY number
FOR XML PATH(N''), TYPE
)
FROM (
SELECT
CASE WHEN EXISTS(SELECT * FROM sys.index_columns ic JOIN sys.all_columns allc ON ic.object_id = allc.object_id AND ic.column_id = allc.column_id WHERE ic.key_ordinal > 0 AND allc.is_nullable = 1 AND ic.object_id = ix.object_id AND ic.index_id = IX.index_id HAVING COUNT(*) > 1)
THEN 1
ELSE 0
END
) mc(IsKeyMultiColumn)
WHERE @Analysis > 0
FOR XML RAW('Warning'), TYPE
) Warnings,
ix.object_id ObjId
FROM sys.indexes ix
OUTER APPLY (
SELECT RCCount = SUM(pattis.rows)
FROM sys.partitions pattis
WHERE pattis.object_id = ix.object_id
AND pattis.index_id = ix.index_id
) rcc
JOIN sys.objects objc ON ix.object_id = objc.object_id
JOIN sys.schemas sch ON objc.schema_id = sch.schema_id
OUTER APPLY (
SELECT NULL is_ignored_in_optimization, NULL suppress_dup_key_messages, NULL auto_created, NULL optimize_for_sequential_key
) odlv
-- Spatial
LEFT JOIN sys.spatial_indexes spatil ON spatil.object_id = ix.object_id AND spatil.index_id = ix.index_id
LEFT JOIN sys.spatial_index_tessellations spatle ON spatil.object_id = spatle.object_id AND spatil.index_id = spatle.index_id
-- XML
LEFT JOIN sys.xml_indexes xmli ON xmli.object_id = ix.object_id AND xmli.index_id = ix.index_id
-- Hash
LEFT JOIN sys.hash_indexes hsh ON hsh.object_id = ix.object_id AND hsh.index_id = ix.index_id
-- Others
LEFT JOIN sys.key_constraints kcois ON ix.object_id = kcois.parent_object_id AND ix.index_id = kcois.unique_index_id
OUTER APPLY (
SELECT STUFF( (SELECT N',' + QUOTENAME(alc.name) ++ CASE WHEN ixlms.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
FROM sys.index_columns ixlms JOIN sys.all_columns alc ON ixlms.object_id = alc.object_id AND ixlms.column_id = alc.column_id
WHERE ixlms.is_included_column = 0
AND ixlms.object_id = ix.object_id
AND ixlms.index_id = ix.index_id
ORDER BY ixlms.key_ordinal
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) ky(KColumns)
OUTER APPLY (
SELECT STUFF( (SELECT N',' + QUOTENAME(alc.name)
FROM sys.index_columns ixlms JOIN sys.all_columns alc ON ixlms.object_id = alc.object_id AND ixlms.column_id = alc.column_id
WHERE ixlms.is_included_column = 1
AND ixlms.object_id = ix.object_id
AND ixlms.index_id = ix.index_id
ORDER BY ixlms.key_ordinal
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
) ic(NColumns)
LEFT JOIN (
SELECT ideps.index_id,avg_fragmentation_in_percent AvgFragmentation, avg_page_space_used_in_percent AvgPageSpaceUsed
FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjId, NULL, NULL, 'SAMPLED') ideps
WHERE @Analysis > 0
) frang ON frang.index_id = ix.index_id
WHERE ix.object_id = @ObjId
) q
OPTION(RECOMPILE)
-- Current Activity
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) >= 14 -- Minimum SQL2017
BEGIN
SELECT Descr = 'Current Activity = Index Resumable Operations', *
FROM sys.index_resumable_operations irseas
WHERE irseas.object_id = @ObjId
END
DECLARE @Session TABLE (session_id SMALLINT, start_time DATETIME, status VARCHAR(20), db SYSNAME, wait_type SYSNAME, wait_time BIGINT, open_transaction_count SMALLINT, query VARCHAR(MAX), wait_resource VARCHAR(700), rstype AS (LEFT(wait_resource, 4)), wait_resource_xml XML, Ceva BIGINT, Altceva BIGINT, Totalaltceva BIGINT, CCeva BIGINT)
INSERT @Session (session_id, start_time, status, db, wait_type, wait_time, open_transaction_count, query, wait_resource)
SELECT req.session_id, req.start_time, req.status, db = DB_NAME(database_id), wait_type, wait_time, open_transaction_count, q.text, req.wait_resource
FROM sys.dm_exec_requests req
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) q
WHERE NULLIF(req.wait_resource, '') IS NOT NULL;
IF EXISTS(SELECT * FROM @Session)
BEGIN
WITH UpdateSess
AS (
SELECT *,
SCeva = s_wait_resource_xml.value('i[2]', 'SMALLINT'),
SAltceva = s_wait_resource_xml.value('i[3]', 'BIGINT'),
STotalaltceva = CASE WHEN LEFT(wait_resource, 4) IN ('PAGE', 'RID:') THEN s_wait_resource_xml.value('i[4]', 'BIGINT') END
FROM @Session s
OUTER APPLY (
SELECT s_wait_resource_xml = CONVERT(XML, '<i>' + REPLACE(REPLACE(REPLACE(REPLACE(s.wait_resource, ' ', ''), ':', '</i><i>'), '(', '</i><i>'), ')', '<i></i>') + '</i>')
) castxml
WHERE LEFT(s.wait_resource, 4) IN ('KEY:', 'TAB:', 'OBJE', 'PAGE', 'RID:')
)
UPDATE UpdateSess
SET Ceva = SCeva,
Altceva = SAltceva,
Totalaltceva = STotalaltceva,
wait_resource_xml = s_wait_resource_xml;
WITH UpdateWr
AS (
SELECT CCeva,
CCeva2 = CASE
WHEN rstype = 'KEY:' THEN (SELECT TOP(1) ps.object_id FROM sys.partitions ps WHERE ps.hobt_id = rsc.Altceva) -- Hobt
WHEN rstype IN('OBJE', 'TAB:') THEN rsc.Altceva -- Obje, Tb
END
FROM @Session rsc
WHERE rstype IN ('KEY:', 'OBJE')
)
UPDATE UpdateWr
SET CCeva = CCeva2
BEGIN
DECLARE @PageResults TABLE(ParentObject VARCHAR(50), [Object] VARCHAR(50), Field VARCHAR(50), [VALUE] VARCHAR(50))
DECLARE @Ceva BIGINT, @Altceva BIGINT, @Totalaltceva BIGINT, @CCeva BIGINT
DECLARE CrsResources CURSOR LOCAL READ_ONLY STATIC FORWARD_ONLY
FOR
SELECT DISTINCT Ceva, Altceva, Totalaltceva
FROM @Session rsc
WHERE rstype IN ('PAGE', 'RID:')
AND Ceva IS NOT NULL
AND Altceva IS NOT NULL
AND Totalaltceva IS NOT NULL
OPEN CrsResources
WHILE 1=1
BEGIN
FETCH NEXT FROM CrsResources INTO @Ceva, @Altceva, @Totalaltceva
IF @@FETCH_STATUS <> 0
BEGIN
BREAK -- Go Homesd
END
BEGIN TRY
DECLARE @SqlDBCC VARCHAR(500)
SELECT @SqlDBCC = 'DBCC PAGE (' + LTRIM(@Ceva) + ', ' + LTRIM(@Altceva) + ', ' + LTRIM(@Totalaltceva) + ', 0) WITH TABLERESULTS'
INSERT @PageResults
EXEC(@SqlDBCC) -- Metadata: ObjectId
SELECT @CCeva = CAST(paresl.[VALUE] AS INT) FROM @PageResults paresl
WHERE paresl.Field = 'Metadata: ObjectId'
UPDATE rsc
SET CCeva = @CCeva
FROM @Session rsc
WHERE rsc.Ceva = @Ceva
AND rsc.Altceva = @Altceva
AND rsc.Totalaltceva = @Totalaltceva
END TRY
BEGIN CATCH
-- Sorry. It's some time for Metallica
END CATCH
END
CLOSE CrsResources
DEALLOCATE CrsResources
END
IF EXISTS(SELECT * FROM @Session WHERE CCeva = @ObjID)
BEGIN
SELECT session_id, start_time, [status], db, wait_type, wait_time, open_transaction_count, query, wait_resource, rstype
FROM @Session rsc
WHERE CCeva = @ObjID
END
END
-- FTS
IF EXISTS (SELECT * FROM sys.fulltext_indexes fts WHERE fts.object_id = @ObjId)
BEGIN
SELECT catalog = ctlgs.name,
iuc.name,
(
SELECT name = QUOTENAME(acls.name),
language = lngs.name,
statistical_semantics,
file_type = tpes.name
FROM sys.fulltext_index_columns ftscls
JOIN sys.all_columns acls ON ftscls.object_id = acls.object_id AND ftscls.column_id = acls.column_id
JOIN sys.fulltext_languages lngs ON lngs.lcid = ftscls.language_id
LEFT JOIN sys.all_columns tpes ON ftscls.object_id = acls.object_id AND ftscls.type_column_id = tpes.column_id
WHERE ftscls.object_id = fts.object_id
FOR XML RAW, TYPE
) catalog_fields,
fts.is_enabled,
fts.change_tracking_state_desc,
fts.crawl_type_desc,
fts.has_crawl_completed,
fts.crawl_start_date,
fts.crawl_end_date
FROM sys.fulltext_indexes fts
JOIN sys.indexes iuc ON fts.object_id = iuc.object_id AND fts.unique_index_id = iuc.index_id
JOIN sys.fulltext_catalogs ctlgs ON ctlgs.fulltext_catalog_id = fts.fulltext_catalog_id
OUTER APPLY (
SELECT NULL statistical_semantics
) cmtb
WHERE fts.object_id = @ObjId
END
-- Missing Indexess
IF @Analysis = 1
BEGIN
DECLARE @Plans TABLE ([Plan] XML NOT NULL, PlanHandle VARBINARY(64) NOT NULL)
DECLARE @Missing TABLE (Fragment XML NULL, Settings XML NULL, Query NVARCHAR(MAX), CreateIndex NVARCHAR(MAX))
INSERT @Plans ([Plan], PlanHandle)
SELECT TOP(10) [Plan], plan_handle
FROM sys.dm_exec_query_stats qst
OUTER APPLY (
SELECT [Plan] = xl.query_plan
FROM sys.dm_exec_query_plan(qst.plan_handle) xl
) q
OUTER APPLY q.[plan].nodes('//*:MissingIndex') mi(Missig)
WHERE qst.last_execution_time >= DATEADD(MINUTE, -3, GETDATE())
AND mi.Missig.value('@Table[1]', 'SYSNAME') = @CurrObj
AND mi.Missig.value('@Schema[1]', 'SYSNAME') = @CurrSchema
ORDER BY qst.total_logical_reads DESC
DECLARE Crs CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT pa.[Plan]
FROM (
SELECT DISTINCT PlanHandle
FROM @Plans
) pb
OUTER APPLY (
SELECT TOP(1) p.[Plan], PlanHandle
FROM @Plans p
WHERE p.PlanHandle = pb.PlanHandle
) pa
OPEN Crs
DECLARE @Plan XML
WHILE 1=1
BEGIN
FETCH NEXT FROM Crs INTO @Plan
IF @@FETCH_STATUS <> 0
BEGIN
BREAK
END
INSERT @Missing (Fragment, Settings, Query, CreateIndex)
SELECT Fragment = SqlStatements.Nod.query('.'),
Settings.*,
Query =
CASE
WHEN SqlStatements.Nod.value('local-name(../../../..)', 'SYSNAME') = 'Statements' THEN SqlStatements.Nod.value('(../../../@StatementText)[1]', 'NVARCHAR(MAX)')
WHEN SqlStatements.Nod.value('local-name(../../..)', 'SYSNAME') = 'Statements'THEN SqlStatements.Nod.value('(../../@StatementText)[1]', 'NVARCHAR(MAX)')
END,
CreateIndex =
(SELECT '--The Query Processor estimates that implementing the following index could improve the query cost by ' + LTRIM(obc.ProcImpact) + '%.' + CHAR(13) + CHAR(10) +
'USE ' + obc.Db + CHAR(13) + CHAR(10) +
'GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT ON
GO' + CHAR(13) + CHAR(10) +
'CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]' + CHAR(13) + CHAR(10) +
'ON ' + obc.[Object] + ' (' + ikey.Cols + ')' + ISNULL(CHAR(13) + CHAR(10) +
'INCLUDE (' + iinclude.Cols + ')', ''))
FROM @Plan.nodes('//*:MissingIndexes') SqlStatements(Nod)
CROSS
APPLY SqlStatements.Nod.nodes('*:MissingIndexGroup') mixg(Nod)
CROSS
APPLY mixg.Nod.nodes('*:MissingIndex') mix(Nod)
CROSS
APPLY (
SELECT
mixg.Nod.value('(@Impact)[1]', 'DECIMAL(6,3)'),
mix.Nod.value('(@Database)[1]', 'SYSNAME'),
mix.Nod.value('(@Schema)[1]', 'SYSNAME') + '.' + mix.Nod.value('(@Table)[1]', 'SYSNAME'),
mix.Nod.value('(@Schema)[1]', 'SYSNAME'),
mix.Nod.value('(@Table)[1]', 'SYSNAME')
) obc(ProcImpact, [Db], [Object], CurrSchema, CurrObj)
CROSS
APPLY (
SELECT STUFF(
(SELECT ', ' + cols.Nod.value('(@Name)[1]', 'NVARCHAR(MAX)')
FROM mix.Nod.nodes('*:ColumnGroup[@*:Usage = ("EQUALITY", "INEQUALITY")]/*:Column') cols(Nod)
ORDER BY cols.Nod.value('(../@Usage)[1]', 'VARCHAR(20)')/*EQU, INE*/, cols.Nod.value('(@ColumnId)[1]', 'INT')
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) ikey(Cols)
CROSS
APPLY (
SELECT STUFF(
(SELECT ', ' + cols.Nod.value('(@Name)[1]', 'NVARCHAR(MAX)')
FROM mix.Nod.nodes('*:ColumnGroup[@*:Usage = ("INCLUDE")]/*:Column') cols(Nod)
ORDER BY cols.Nod.value('(@ColumnId)[1]', 'INT')
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) iinclude(Cols)
OUTER APPLY (
SELECT
CASE
WHEN SqlStatements.Nod.value('local-name(../../../..)', 'SYSNAME') = 'Statements' THEN SqlStatements.Nod.query('(../../../*:StatementSetOptions)')
WHEN SqlStatements.Nod.value('local-name(../../..)', 'SYSNAME') = 'Statements'THEN SqlStatements.Nod.query('(../../*:StatementSetOptions)')
END
) line(Settings)
OUTER APPLY (
SELECT
[QUOTED_IDENTIFIER] = NULLIF(line.Settings.value('(*/@QUOTED_IDENTIFIER)[1]','SYSNAME'), 'true'),
[ANSI_NULLS] = NULLIF(line.Settings.value('(*/@ANSI_NULLS)[1]','SYSNAME'), 'true'),
[CONCAT_NULL_YIELDS_NULL] = NULLIF(line.Settings.value('(*/@CONCAT_NULL_YIELDS_NULL)[1]','SYSNAME'), 'true'),
[ARITHABORT] = NULLIF(line.Settings.value('(*/@ARITHABORT)[1]','SYSNAME'), 'true'),
[ANSI_PADDING] = NULLIF(line.Settings.value('(*/@ANSI_PADDING)[1]','SYSNAME'), 'true'),
[ANSI_WARNINGS] = NULLIF(line.Settings.value('(*/@ANSI_WARNINGS)[1]','SYSNAME'), 'true'),
[NUMERIC_ROUNDABORT] = NULLIF(line.Settings.value('(*/@NUMERIC_ROUNDABORT)[1]','SYSNAME'), 'false')
FOR XML RAW('Warning'), TYPE
) settings(Settings)
WHERE obc.CurrSchema = @CurrSchema
AND obc.CurrObj = @CurrObj
END
CLOSE Crs
DEALLOCATE Crs
IF EXISTS(SELECT * FROM @Missing)
SELECT * FROM @Missing
ELSE
SELECT 'Zero Missing Indexes Found' Rez
END
END
No comments:
Post a Comment