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