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 index
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.
