November 7, 2018 at 8:16 am
I know various forms of this question have been asked. However mine I think is a bit different.
Using SSIS I pull in large amounts of data from another database. Currently almost all tables are truncated and new data is inserted. A few of the tables have over 50 million records. On those tables I disable the indexes, pull in the data and then rebuild the indexes. Since they have a clustered index I cannot use the ALTER INDEX ALL ON XXX DISABLE command. I want to disable all the non clustered indexes only. I of course can do that manually for each index. This doesn't help when a new non clustered index is created and then doesn't get added to disable into the SSIS SQL Execute command.
Is there a way to disable only non clustered indexes without having to write out a disable command for each non clustered index?
Note : the data in these tables are static until the next time it is truncated and new data is uploaded (nightly SSIS loads).
Troy
November 7, 2018 at 8:24 am
Looks like I need to run a command like this (found on stackoverflow):
declare @Indexes table
(
Num int identity(1,1) primary key clustered,
TableName nvarchar(255),
IndexName nvarchar(255)
)
INSERT INTO @Indexes
(
TableName,
IndexName
)
SELECT sys.objects.name tableName,
sys.indexes.name indexName
FROM sys.indexes
JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id
WHERE sys.indexes.type_desc = 'NONCLUSTERED'
AND sys.objects.type_desc = 'USER_TABLE'
DECLARE @max-2 INT
SET @max-2 = @@ROWCOUNT
SELECT @max-2 as 'max'
SELECT * FROM @Indexes
DECLARE @I INT
SET @I = 1
DECLARE @TblName NVARCHAR(255), @IdxName NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)
WHILE @I <= @max-2
BEGIN
SELECT @TblName = TableName, @IdxName = IndexName FROM @Indexes WHERE Num = @I
SELECT @SQL = N'ALTER INDEX ' + @IdxName + N' ON ' + @TblName + ' DISABLE;'
EXEC sp_sqlexec @SQL
SET @I = @I + 1
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply