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.

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s