How to check if I can remove index from DB in SQL Server

After large redesigning of DB I need to remove all indices from it, and set new indices.

I found good scrpipt to get all indices and remove but I have problem - I cannot remove indices created by PRIMARY KEY constraint. (maybe there are also other kind of indices that I cannot remove).

My question is: how to change code below to remove all indices except indices created for primary keys or other that I cannot remove manually.

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'

OPEN [indexes]

FETCH NEXT FROM [indexes] INTO @indexName, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC 开发者_Go百科('DROP INDEX [' + @indexName + '] ON [' + @tableName + ']')
    FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END

CLOSE [indexes]
DEALLOCATE [indexes]

You can try to use the sys.indexes catalog view instead of the deprecated sysindexes.

That catalog view has a column call is_primary_key, so you should be able to find all non-primary key indices like this:

SELECT *
FROM sys.indexes
WHERE is_primary_key = 0

You can easily create your DROP INDEX statements from this:

SELECT 'DROP INDEX ' + name + ' ON ' + object_name(object_id)
FROM sys.indexes
WHERE is_primary_key = 0
AND object_ID > 255     -- exclude system-level tables with object_id <= 255
AND name IS NOT NULL    -- exclude heaps with a NULL index name

Just copy&paste the output of this statement, and run that - and you should be done.

Based on Marc_s answer I found correct version of script:

SELECT 'DROP INDEX ' + name + ' ON ' + object_name(object_id)
FROM sys.indexes
WHERE is_primary_key = 0 AND is_unique_constraint = 0
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0     -- exclude system-level tables
AND name IS NOT NULL

Checking if object_Id is greater than 255 isn't sufficient:
- we had to use OBJECTPROPERTY(object_id, 'IsMSShipped') = 0, because some system tables like queue_messages have very high id
- also we have to check if index is created by unique constraint : is_unique_constraint = 0

After those improvements it'll remove all indices created by user.