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.



TODAY – Virtual Launch Event for SQL Server 2012

In a few hours Microsoft will arrange a large online launch event for new SQL Server 2012.

The keynotes will be accompanied by 30+ sessions presented in three tracks:

  • Mission Critical Confidence
  • Breakthrough Insights
  • Cloud on your Terms