Get Index Definition with Include Column List
One of my favorite features added to SQL server was the ability to create indexes and have additional columns included in the index, not participating in the key values.
This capability increases the capability of creating useful covering indexes. Because you can include columns not participating in the key of the index, you can now include data types in a covering index previously not supported.
I have not found out how to identify columns included in an index that are not part of the key using the standard SQL Server tools. Perhaps you know and will drop me an Email to share with our readers. I have tried the traditional sp_helpindex and using SQL Server Manaement Studio. From what I have seen, these tools only demonstrate the key columns in an index.
I wrote a quick stored procedure I’m sharing today that works similar to sp_helpindex. The main difference is that it not only provides a list of columns used for the key of the index, but it also provides a list of included columns.
If you have the better solution of something already packaged in SQL Server please send me an Email to post later at btaylor@sswug.org.
Cheers,
Ben
Script Includes one function and one stored procedure
************************************************************
PRINT 'FUNCTION sfn_GetIndexColumnList'
GO
IF OBJECT_ID('sfn_GetIndexColumnList') IS NOT NULL
BEGIN
PRINT ' DROP FUNCTION sfn_GetIndexColumnList'
EXEC ('DROP FUNCTION sfn_GetIndexColumnList')
END
GO
PRINT ' CREATE FUNCTION sfn_GetIndexColumnList'
GO
CREATE FUNCTION sfn_GetIndexColumnList (
@ObjectID INT
,@IndexID INT, @isInclude BIT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @IndexColList VARCHAR(MAX) = ''
SELECT @IndexColList += ', ' + c.name
FROM sys.index_columns ic
join sys.columns c on c.object_id = @ObjectID
and ic.column_id = c.column_id
where ic.object_id = @ObjectID
and ic.Index_id = @IndexID
AND ic.is_included_column = @isInclude
ORDER BY ic.index_column_id
RETURN SUBSTRING(@IndexColList,3, 8000)
END
GO
IF OBJECT_ID('usp_HelpIndex_With_Include') IS NOT NULL
BEGIN
PRINT ' DROP PROCEDURE usp_HelpIndex_With_Include'
DROP PROCEDURE usp_HelpIndex_With_Include
END
GO
PRINT ' CREATE PROCEDURE usp_HelpIndex_With_Include'
GO
/**********************************************************************
PROCEDURE usp_HelpIndex_With_Include
***********************************************************************
Author Benjamion Taylor
Created 10/21/2011
Description Return Index Definition with Index Columns and
Include Columns
No warranty provided or implied. Use at your own risk.
***********************************************************************
TEST
EXEC usp_HelpIndex_With_Include 'SomeTableName'
**********************************************************************/
CREATE PROCEDURE usp_HelpIndex_With_Include
(
@Object_Name SYSNAME
)
AS
SET NOCOUNT ON
DECLARE @DBName SYSNAME = DB_NAME()
DECLARE @CMD VARCHAR(8000)
SET @CMD = REPLACE('
SELECT
DBName = ''[DBName]''
,Schema_Name = s.name
,Object_Name = so.name
,Index_Name = i.name
,FileGroupName = fg.name
,Index_Cols = SysAdmin.dbo.sfn_GetIndexColumnList(@DBName, i.object_id, i.index_id, 0)
,Include_Cols = SysAdmin.dbo.sfn_GetIndexColumnList(@DBName, i.object_id, i.index_id, 1)
FROM [DBName].sys.objects so
join [DBName].sys.schemas s on so.schema_id = s.schema_id
join [DBName].sys.indexes i on i.object_id = so.object_id
join [DBName].sys.filegroups fg on i.data_space_id = fg.data_space_id
WHERE so.Name = ''' + @Object_Name + '''',
'[DBName]', @DBName)
EXEC (@CMD)
GO
IF @@ERROR = 0
BEGIN
PRINT ' PROCEDURE usp_HelpIndex_With_Include CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT ' CREATE PROCEDURE usp_HelpIndex_With_Include FAILED!!!'
END
GO
$$SWYNK$$
Featured Article(s)
How to Handle Multiple and Unclear Job Directives
Whether we are in the corporate environment or starting our own small business, we have competing resources for our attention, skills and talents. How can we not only professionally handle these multiple and unclear job directives, but use these opportunities to propel us forward faster?
Featured White Paper(s)
Demystify Tempdb Performance and Management
In this white paper, Robert L Davis demystifies the best practices that work some of the time, but not all of the time, when … (read more)