1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| DECLARE @nomTab varchar(128)
DECLARE @nomIndex varchar(128)
DECLARE @SQL varchar(8000)
DECLARE dropIndex CURSOR
FOR
SELECT DISTINCT i.name, o.name
FROM sysindexes i
INNER JOIN sysobjects o ON o.id=i.id
INNER JOIN syscolumns c ON c.id=o.id
WHERE (o.name LIKE 'C\_%' ESCAPE '\' OR o.name LIKE 'A\_%' ESCAPE '\' )
AND i.name NOT IN (SELECT *,CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = o.name)
AND indid >=1
OPEN dropIndex
FETCH dropIndex INTO @nomIndex,@nomTab
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'DROP INDEX [' + @nomTab + '].[' + @nomIndex +' ]';
EXEC (@SQL);
IF @@ERROR = 0
PRINT @SQL;
ELSE
PRINT 'Index ' + @nomIndex ' non supprimé sur la table ' + @nomTab;
FETCH NEXT FROM dropIndex INTO @nomIndex,@nomTab
END
CLOSE dropIndex
DEALLOCATE dropIndex |
Partager