How To: Generate a script to drop all Indexes

Well…here it is!
I know some of you are thinking, why would you ever do that?
In my case, I had to upload the entire database (about 40GB) to an external FTP-server.
It saved me a few GB’s to drop the indexes first and send them a SQL-script to (re-)create them 😉

SELECT
'DROP INDEX ' + SS.name + '.' + SO.name + '.' + '[' + SI.name + ']'
FROM sys.objects as SO
INNER JOIN sys.schemas SS
ON SO.schema_id = SS.schema_id
INNER JOIN sys.indexes as SI
ON SO.Object_Id = SI.Object_Id
WHERE LEFT(si.Name,2) IN ('IX') --'IX' is the used naming prefix.
ORDER BY SO.Name , SI.name

Advertisements

New SQL Server 2012 Labs and Presentations Available

Microsoft released (and updated) the SQL Server 2012 Developer Training Kit and you can get it right now via the Web Installer Preview:

Microsoft’s Download Center

A complete list of the content can be found here.