Few days ago, I was working on a client database and faced some issues with ColumnStore index and to fix those issue I had to develop certain tools. Given below is the list of tools I developed :
- How to find all columnstore indexes with column name and datatype in a particular database ?
- How to disable all columnstore indexes in a particular database ?
- How to rebuild all columnstore indexes in a particular database ?
- How to drop all columnstore indexes in a particular database ?
Given below is the script of “How to find all columnstore indexes with column name and datatype in a particular database? ” This script not only works for columnstore indexes but also gives the list of all indexes of any particular database as well.
CREATE PROCEDURE dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] @Type int AS BEGIN ;WITH CTE AS ( SELECT TBL.object_id AS [Object ID] , schema_name(schema_id) AS [Schema Name] , tbl.name AS [Table Name] , i.name AS [Index Name] , i.type AS [Index type] , i.type_desc AS [Index Desc] , clmns.name AS [Column Name] , styps.name AS [Type Name] FROM sys.tables AS tbl INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id) INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 OR ic.partition_ordinal = 0 OR ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id) INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id AND clmns.column_id = ic.column_id INNER JOIN sys.systypes AS styps ON clmns.system_type_id=styps.type WHERE ((1=(CASE WHEN @Type is Null THEN 1 ELSE 0 END) OR i.type = @Type))) --To convert all datatypes into one row SELECT [Object ID] , [Schema Name] , [Table Name] , [Index Name] , [Index Desc] , STUFF((SELECT ', ' + [Column Name] + '(' + [Type Name] + ')' FROM CTE AS CTE1 WHERE CTE1.[Object ID]= CTE2. [Object ID] FOR XML PATH('')),1,1,'') as [Column Name with datatype] FROM CTE AS CTE2 GROUP BY [Object ID] ,[Schema Name] ,[Table Name] ,[Index Name] ,[Index Desc] ORDER BY [Object ID],[Schema Name],[Table Name] END GO -- LIST OF NONCLUSTERED COLUMNSTORE INDEX USE AdventureWorks2012 GO EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 6 GO -- LIST OF HEAP USE AdventureWorks2012 GO EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 0 GO -- LIST OF CLUSTERED INDEX USE AdventureWorks2012 GO EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 1 GO -- LIST OF NONCLUSTERED INDEX USE AdventureWorks2012 GO EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 2 GO -- LIST OF XML INDEX USE AdventureWorks2012 GO EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 3 GO -- LIST OF SPATIAL INDEX USE AdventureWorks2012 GO EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] 4 GO -- LIST OF ALL INDEX USE AdventureWorks2012 GO EXEC dbo.[SEARCH_LIST_OF_ALL_INDEXES_PROC] NULL GO
Result : List of all NON-CLUSTERED COLUMNSTORE INDEX
Shall post other related tools (how to disable, rebuild and drop columnstore indexes) in my upcoming posts.