Ho risolto il problema e visto che potrebbe essere utile a qualcuno posto qui il codice corretto.
codice:
USE nome_database
DECLARE @table_name sysname
DECLARE @index_name sysname
-- creo ed eseguo il ciclo sulle tabelle
DECLARE table_cursor CURSOR LOCAL FAST_FORWARD FOR
--legge i nomi delle tabelle
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE not like 'view%') AND
(TABLE_NAME NOT IN(select TABLE_NAME FROM
INFORMATION_SCHEMA.TABLE_PRIVILEGES))FOR READ ONLY
OPEN table_cursor
FETCH table_cursor INTO @table_name
WHILE (@@fetch_status <> -1)
BEGIN
-- creo ed eseguo il ciclo sugli indici
DECLARE index_cursor CURSOR FOR
SELECT sysindexes.name
FROM sysobjects, sysindexes,
INFORMATION_SCHEMA.TABLES
WHERE
INFORMATION_SCHEMA.TABLES.TABLE_NAME = sysobjects.name
AND sysobjects.[id] = sysindexes.[id]
AND sysobjects.name IN (SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE NOT LIKE 'view%')
AND sysindexes.indid <> 255
AND sysindexes.indid <> 0
AND sysindexes.status > 0
AND sysobjects.status > 0
AND NOT sysindexes.rowmodctr < 0
AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = @table_name
ORDER BY sysindexes.name, sysindexes.status
OPEN index_cursor
FETCH index_cursor INTO @index_name
WHILE (@@fetch_status <> -1)
BEGIN
EXEC('Drop index ' + @table_name + '.' + @index_name)
-- mi sposto sull'indice successivo
FETCH NEXT FROM index_cursor INTO @index_name
END
CLOSE index_cursor
DEALLOCATE index_cursor
-- mi sposto sulla tabella successiva
FETCH NEXT FROM table_cursor INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
spero possa essere utile.
ciao a tutti
lady