Enable/disable all indexes

If you work with SQL Server on low-level and doing lots of data shuffeling, this is probably not new to you, but today I needed to generate ALTER INDEX statements when importing large amount of rows into some tables.

SELECT 'ALTER INDEX ' + i.name+ ' ON ' + o.name+ ' DISABLE ' + CHAR(13) + Char(10)+';' 
FROM sys.indexes i   
   INNER JOIN sys.objects o ON o.object_id = i.object_id 
WHERE o.is_ms_shipped = 0
  AND i.index_id >= 1 
ORDER BY i.name ;

When you are finished with the import just generate the statement where DISABLE is remplaced with REBUILD, and run the result.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s