I am running a DTS to preform tasks in my database, in which at first I need to disable all indexes in the database and re-enable them when the DTS finish his work.
Is there a way that I can disable all the indexes in the whole database and afterwards to re-enable them all?
I know how to disable/enable one by one, can someone help me with the way to disable/enable all at once as a step in the DTS.
We can use below scrip to disable indexes
ALTER INDEX ALL ON [TableName]
DISABLE;
Do your bulk insert to table and than run below script.
ALTER INDEX ALL ON [TableName]
REBUILD;
Here is a script that will output ALTER statements for all non clustered indexes in your database. You can modify this easily to output REBUILD scripts and scripts for clustered indexes
select 'ALTER INDEX [' + I.name + '] ON [' + T.name + '] DISABLE'
from sys.indexes I
inner join sys.tables T on I.object_id = T.object_id
where I.type_desc = 'NONCLUSTERED'
and I.name is not null
This works for SQL Server 2008 and newer versions. It allows different schemas and also names that have spaces, dashes and other special characters that have to be quoted. What is the use of the square brackets [] in sql statements?
These scripts will output code into the results tab. You must copy/paste into the query tab and execute them.
Disable script
SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' DISABLE'
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 0
Enable script (Rebuild)
SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' REBUILD'
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 1
This is based on another answer here.
In order to enable an index, you have to rebuild it. This script will rebuild all disabled indexes.
DECLARE @my_sql2 NVARCHAR(200);
DECLARE cur_rebuild CURSOR FOR
SELECT 'ALTER INDEX ' + i.name + ' ON ' + t.name + ' REBUILD' FROM sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id WHERE i.is_disabled = 1 ORDER BY t.name, i.name;
OPEN cur_rebuild;
FETCH NEXT FROM cur_rebuild INTO @my_sql2;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @my_sql2;
FETCH NEXT FROM cur_rebuild INTO @my_sql2;
END;
CLOSE cur_rebuild;
DEALLOCATE cur_rebuild;
GO
Disabling indexes is a good idea when it comes to loading large quantities of data, but... the big problem is clustered indexes. If you disable a clustered index, you’ve disabled the entire table.
Several options suggest themselves, and none of them are simple.
1) Loop through the system views (sys.indexes), extract the table and index name, generate and execute dynamic SQL to disable the index. Have an “undo” routine to re-enable them. (Be wary--was it a unique index or a unique constraint?) This, alas, only works if you do not use clustered indexes. Good luck with that.
2) As for 1, but skip any clustered indexes. When you load data, make sure it gets loaded in (clustered index) sequential order, otherwise you'll have poor load times and fragmented tables. (If you data providers are like mine, good luck with that one, too.)
3) Create tables in your database containing definitions of the indexes on your “loading” tables. Build a routine that loops through them and drops all the indexes (clustered indexes last). This will be fast if you truncate the tables first. Load your data, then loop through and recreate the indexes from scratch (clustered first). Use table partitioning to make less horrible on the rest of the system (e.g. do all the above on the “loading” tables, then use partition switching to move the loaded data into your “live” tables). It took me no little time to build such a system, but it can and will work.
Use this script to disable all indices
-- Disable All Indices
DECLARE @Script NVARCHAR(MAX)
DECLARE curIndices CURSOR FAST_FORWARD READ_ONLY FOR
SELECT 'ALTER INDEX ' + QUOTENAME(indices.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(tableNames.schema_id))+'.'+ QUOTENAME(tableNames.name) + ' DISABLE'
FROM
sys.indexes indices INNER JOIN
sys.tables tableNames ON indices.object_id = tableNames.object_id
WHERE
indices.type_desc = 'NONCLUSTERED' AND
indices.name IS NOT NULL AND
indices.is_disabled = 0;
OPEN curIndices
FETCH NEXT FROM curIndices INTO @Script
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Script
EXECUTE sp_executesql @Script
FETCH NEXT FROM curIndices INTO @Script
END
CLOSE curIndices
DEALLOCATE curIndices
Use this script to rebuild(enable) all indices
-- Rebuild All Indices
DECLARE @Script NVARCHAR(MAX)
DECLARE curIndices CURSOR FAST_FORWARD READ_ONLY FOR
SELECT 'ALTER INDEX ' + QUOTENAME(indices.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(tableNames.schema_id))+'.'+ QUOTENAME(tableNames.name) + ' REBUILD'
FROM
sys.indexes indices INNER JOIN
sys.tables tableNames ON indices.object_id = tableNames.object_id
WHERE
indices.type_desc = 'NONCLUSTERED' AND
indices.name IS NOT NULL AND
indices.is_disabled = 1;
OPEN curIndices
FETCH NEXT FROM curIndices INTO @Script
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Script
EXECUTE sp_executesql @Script
FETCH NEXT FROM curIndices INTO @Script
END
CLOSE curIndices
DEALLOCATE curIndices
You will have to run a script that selects the metadate for the table and index. Then you can do an:
ALTER INDEX indexname ON tablename DISABLE;
Later you can run a similar script to rebuild:
ALTER INDEX indexname ON tablename REBUILD;
You can do these one at a time, or collect them into a NVARCHAR(MAX) variable and execute them as a single batch. You can see sample code at this earlier question:
All the index will be enabled by charm!
