How can I drop all indexes in a SQL database with one command?

this worked for me we skip sys indexes and for constraints

declare @qry nvarchar(max);
select @qry = (

    select  'IF EXISTS(SELECT * FROM sys.indexes WHERE name='''+ i.name +''' AND object_id = OBJECT_ID(''['+s.name+'].['+o.name+']''))      drop index ['+i.name+'] ON ['+s.name+'].['+o.name+'];  '
    from sys.indexes i 
        inner join sys.objects o on  i.object_id=o.object_id
        inner join sys.schemas s on o.schema_id = s.schema_id
    where o.type<>'S' and is_primary_key<>1 and index_id>0
    and s.name!='sys' and s.name!='sys' and is_unique_constraint=0
for xml path(''));

exec sp_executesql @qry

From: Stephen Hill's Bloggie

DECLARE @indexName VARCHAR(128)
DECLARE @tableName VARCHAR(128)

DECLARE [indexes] CURSOR FOR

        SELECT          [sysindexes].[name] AS [Index],
                        [sysobjects].[name] AS [Table]

        FROM            [sysindexes]

        INNER JOIN      [sysobjects]
        ON              [sysindexes].[id] = [sysobjects].[id]

        WHERE           [sysindexes].[name] IS NOT NULL 
        AND             [sysobjects].[type] = 'U'
        --AND               [sysindexes].[indid] > 1

OPEN [indexes]

FETCH NEXT FROM [indexes] INTO @indexName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
        --PRINT 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']'
        Exec ('DROP INDEX [' + @indexName + '] ON [' + @tableName + ']')

        FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END

CLOSE           [indexes]
DEALLOCATE      [indexes]

GO

None of the answers quite suited my needs.

I needed one that will also drop indexes that backup unique or primary constraints (except if these can't be dropped as they back up a foreign key)

DECLARE @SqlScript NVARCHAR(MAX);


SELECT @SqlScript = 
(
SELECT 
'
BEGIN TRY
'+ CASE WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN
 '
 ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(t.name)  + ' DROP CONSTRAINT ' + QUOTENAME(i.name) + ';'
else
 '
 DROP INDEX ' + QUOTENAME(i.name)  + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(t.name)
 END+'

END TRY
BEGIN CATCH
RAISERROR(''Could not drop %s on table %s'', 0,1, ' + QUOTENAME(i.name, '''') + ', ' + QUOTENAME(t.name, '''') + ')
END CATCH
'
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc IN ('CLUSTERED', 'NONCLUSTERED' )
ORDER BY t.object_id, i.index_id DESC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');

--Return script that will be run 
SELECT @SqlScript AS [processing-instruction(x)]
FOR XML PATH('');

EXEC (@SqlScript);

You're very close.

declare @qry nvarchar(max);
select @qry = 
(SELECT  'DROP INDEX ' + quotename(ix.name) + ' ON ' + quotename(object_schema_name(object_id)) + '.' + quotename(OBJECT_NAME(object_id)) + '; '
FROM  sys.indexes ix
WHERE   ix.Name IS NOT null and ix.Name like '%prefix_%'
for xml path(''));
exec sp_executesql @qry