25 lines
921 B
Transact-SQL
25 lines
921 B
Transact-SQL
DECLARE @tCsIdx TABLE(
|
|
[schemaName] VARCHAR(50),
|
|
[tableName] VARCHAR(100),
|
|
[indexName] VARCHAR(100),
|
|
[indexType] VARCHAR(50),
|
|
[disableCmd] AS 'ALTER INDEX ['+[indexName]+'] ON ['+[schemaName]+'].['+[tableName]+'] DISABLE;'+CHAR(13)+CHAR(10),
|
|
[enableCmd] AS 'ALTER INDEX ['+[indexName]+'] ON ['+[schemaName]+'].['+[tableName]+'] REBUILD;'+CHAR(13)+CHAR(10)
|
|
);
|
|
|
|
INSERT INTO @tCsIdx ([schemaName], [tableName], [indexName], [indexType])
|
|
|
|
SELECT
|
|
OBJECT_SCHEMA_NAME([i].[object_id]) AS [SchemaName]
|
|
,OBJECT_NAME([i].[object_id]) AS [TableName]
|
|
,[i].[name] AS [IndexName]
|
|
,[i].[type_desc] AS [IndexType]
|
|
FROM [sys].[indexes] AS [i]
|
|
WHERE [i].[is_hypothetical] = 0
|
|
AND [i].[index_id] <> 0
|
|
AND [i].[type_desc] IN ( 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE' )
|
|
AND i.[is_disabled] = 0
|
|
|
|
SELECT *
|
|
FROM @tCsIdx [tci]
|
|
GO |