Query to collect all indexes from a specific table and generate the drop command-CodePudding

I'm trying to code something to select all indexes from a specific table and outupt de command to drop them all, I think is something like that, but the syntax isnt correct at all. Can anyone help? Thanks!

SELECT
  'DROP INDEX '   i.*   ' ON '   TableName = t.Name
    ';'
FROM
  sys.indexes i
  INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE
  T.Name = 'TableXYZ';

CodePudding user response：

This will give, I think, the desired result.

SELECT 'DROP INDEX '   i.name   ' ON '   t.Name   ';' as sql_drop
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE T.Name = 'test'
and i.name is not null;

You can also concatenate with CONCAT:

SELECT concat('DROP INDEX ',i.name,' ON ',t.Name,';') as sql_drop
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE T.Name = 'test'
and i.name is not null;

CodePudding user response：

You must also add QUOTENAME in order to quote the index names correctly. You should also specify the schema.

SELECT
  'DROP INDEX '   QUOTENAME(i.name)   ' ON '   QUOTENAME(s.name)   '.'   QUOTENAME(t.Name)   ';'
FROM
  sys.indexes i
  JOIN sys.tables t ON t.object_id = i.object_id
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE
  t.Name = 'TableXYZ';