tipos - ¿Cómo puedo eliminar todos los índices en una base de datos SQL con un comando?

tipos de indices sql server

Question

Entonces, ¿cómo puedo soltar todos los índices en una base de datos SQL con un comando? Tengo este comando que me dará todas las 20 o más declaraciones de caída, pero ¿cómo puedo ejecutar todas esas declaraciones de este "conjunto de resultados"?

select * from vw_drop_idnex;

Otra variación que me da la misma lista es:

SELECT  ''DROP INDEX '' + ix.Name + '' ON '' + OBJECT_NAME(ID)  AS QUERYLIST
FROM  sysindexes ix
WHERE   ix.Name IS NOT null and ix.Name like ''%pre_%''

Intenté hacer "exec (seleccione cmd de vw_drop_idnex)" y no funcionó. Estoy buscando algo que funciona como un bucle for y ejecuta las consultas una por una.

-----------------------

Con la ayuda de Rob Farleys, el borrador final del guión es:

declare @ltr nvarchar(1024);
SELECT @ltr = ( select ''alter table ''+o.name+'' drop constraint ''+i.name+'';''
  from sys.indexes i join sys.objects o on  i.object_id=o.object_id
  where o.type<>''S'' and is_primary_key=1
  FOR xml path('''') );
exec sp_executesql @ltr;

declare @qry nvarchar(1024);
select @qry = (select ''drop index ''+o.name+''.''+i.name+'';''
  from sys.indexes i join sys.objects o on  i.object_id=o.object_id
  where o.type<>''S'' and is_primary_key<>1 and index_id>0
for xml path(''''));
exec sp_executesql @qry

Answer 1

De: 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

Answer 2

Estas muy cerca

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

Answer 3

Mejoras menores en la respuesta aceptada que tuve que hacer en mi propio caso, principalmente para explicar los esquemas:

declare @qry nvarchar(4000);
select @qry = (select ''drop index [''+s.name+''].[''+o.name+''].[''+i.name+''];''
  from sys.indexes i join sys.objects o on i.object_id=o.object_id 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''
for xml path(''''));
exec sp_executesql @qry

También: en mi caso, no se pudo ejecutar de una sola vez porque el script tiene más de 4000 caracteres. La única forma en la que podía pensar para lidiar con eso era poner un "top 20" en la selección interna y ejecutarlo varias veces.

Answer 4

Ninguna de las respuestas satisfacía mis necesidades.

Necesitaba uno que también elimine los índices que respaldan restricciones únicas o primarias (excepto si no se pueden eliminar ya que hacen una copia de seguridad de una clave externa)

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);

Answer 5

esto funcionó para mí, nos saltamos los índices de sys y para constantes

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