Disable and re-enable all indexes in a SQL Server database

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

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;

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