August 26, 2010 at 4:32 am
How can i find "include columns" in any index ?
sys.sysindexes doesn't provide its metadata information
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 26, 2010 at 4:41 am
sys.index_columns has a 0/1 column named is_included_column;
here's a script example that pulls some of that info out into a comma delimited list that i use for scripting out indexes:
SELECT
SCH.schema_id, SCH.[name] AS schema_name,
OBJS.[object_id], OBJS.[name] AS object_name,
IDX.index_id, ISNULL(IDX.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type, IDX.type_desc, IDX.fill_factor,
IDX.is_unique, IDX.is_primary_key, IDX.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM tempdb.sys.objects OBJS
INNER JOIN tempdb.sys.schemas SCH ON OBJS.schema_id=SCH.schema_id
INNER JOIN tempdb.sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]
INNER JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM tempdb.sys.dm_db_partition_stats STATS
GROUP BY [object_id], index_id
) AS partitions
ON IDX.[object_id]=partitions.[object_id]
AND IDX.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT COLS.[name] + ',' + ' '
FROM tempdb.sys.index_columns IXCOLS
INNER JOIN tempdb.sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT COLS.[name] + ',' + ' '
FROM tempdb.sys.index_columns IXCOLS
INNER JOIN tempdb.sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
ORDER BY
SCH.[name],
OBJS.[name],
IDX.[name]
:
Lowell
August 26, 2010 at 4:52 am
Bhuvnesh (8/26/2010)
sys.sysindexes doesn't provide its metadata information
No it won't. sysindexes is deprecated and only included for backward compatibility with SQL 2000. Since SQL 2000 did not have include columns, that view won't show them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2010 at 4:57 am
Bhuvnesh (8/26/2010)
@lowelli am not able to see the required "include column" . i can only see the system related info.
run my script against any database; look at the column index_columns_include (last in the list)
you can then rip apart the code for more details.
Lowell
August 26, 2010 at 5:39 am
doh the script i posted was coded for tempdb specifically, this will work in any old database:
SELECT
SCH.schema_id, SCH.[name] AS schema_name,
OBJS.[object_id], OBJS.[name] AS object_name,
IDX.index_id, ISNULL(IDX.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type, IDX.type_desc, IDX.fill_factor,
IDX.is_unique, IDX.is_primary_key, IDX.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM sys.objects OBJS
INNER JOIN sys.schemas SCH ON OBJS.schema_id=SCH.schema_id
INNER JOIN sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]
INNER JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats STATS
GROUP BY [object_id], index_id
) AS partitions
ON IDX.[object_id]=partitions.[object_id]
AND IDX.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT COLS.[name] + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT COLS.[name] + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
ORDER BY
SCH.[name],
OBJS.[name],
IDX.[name]
Lowell
August 26, 2010 at 5:44 am
Lowell (8/26/2010)
doh the script i posted was coded for tempdb specifically, this will work in any old database:
SELECT
SCH.schema_id, SCH.[name] AS schema_name,
OBJS.[object_id], OBJS.[name] AS object_name,
IDX.index_id, ISNULL(IDX.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type, IDX.type_desc, IDX.fill_factor,
IDX.is_unique, IDX.is_primary_key, IDX.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM sys.objects OBJS
INNER JOIN sys.schemas SCH ON OBJS.schema_id=SCH.schema_id
INNER JOIN sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]
INNER JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats STATS
GROUP BY [object_id], index_id
) AS partitions
ON IDX.[object_id]=partitions.[object_id]
AND IDX.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT COLS.[name] + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT COLS.[name] + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
ORDER BY
SCH.[name],
OBJS.[name],
IDX.[name]
YES it works 🙂 thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply