6 Replies - 460 Views - Last Post: 21 September 2016 - 10:55 AM

#1 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 828
  • View blog
  • Posts: 2,235
  • Joined: 23-December 08

Delete all existing Indices (Indexes?) from a list

Posted 20 September 2016 - 06:13 PM

Hey all,

I want to drop all indexes that exist from a list. For example:
Drop all Indeces returned from: SELECT * FROM sys.indexes WHERE object_id = object_id('myTable') AND NAME in ('idx_name', 'idx_age', 'idx_somethingElse', 'idx_favoriteFood')



Can this be done nicely or will I have to write out each one individually? Is simply deleting the data from the table equivalent to deleting the index?

Haaaaalp!

Is This A Good Question/Topic? 0
  • +

Replies To: Delete all existing Indices (Indexes?) from a list

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2340
  • View blog
  • Posts: 9,388
  • Joined: 03-December 12

Re: Delete all existing Indices (Indexes?) from a list

Posted 20 September 2016 - 06:38 PM

Unless you want a stored procedure, a loop is likely needed.
Was This Post Helpful? 0
  • +
  • -

#3 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 828
  • View blog
  • Posts: 2,235
  • Joined: 23-December 08

Re: Delete all existing Indices (Indexes?) from a list

Posted 21 September 2016 - 07:01 AM

Do you mean a loop in the code or in SQL Server? I'll admit this isn't my forte.

If you mean in SQL Server, how would something like that look?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13552
  • View blog
  • Posts: 54,077
  • Joined: 12-June 08

Re: Delete all existing Indices (Indexes?) from a list

Posted 21 September 2016 - 07:08 AM

In theory you would get all the table names, and use a cursor to pull out the table name and use it in the 'drop index' bit.. though, for me, I would spend the extra ten minutes explicitly listing out what tables I want to drop the indexes on and save that sql somewhere versus risking snagging a table more (or less) than I need.
Was This Post Helpful? 0
  • +
  • -

#5 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2340
  • View blog
  • Posts: 9,388
  • Joined: 03-December 12

Re: Delete all existing Indices (Indexes?) from a list

Posted 21 September 2016 - 07:15 AM

If you want to go the SP route,

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


Source


We use SQL Server as our backend, but the DBA I would question on the best route is home today.
Was This Post Helpful? 1
  • +
  • -

#6 atraub  Icon User is offline

  • Pythoneer
  • member icon

Reputation: 828
  • View blog
  • Posts: 2,235
  • Joined: 23-December 08

Re: Delete all existing Indices (Indexes?) from a list

Posted 21 September 2016 - 07:21 AM

I appreciate it. Sadly, my peers are VERY anti-StoredProcedure. :whatsthat:
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2340
  • View blog
  • Posts: 9,388
  • Joined: 03-December 12

Re: Delete all existing Indices (Indexes?) from a list

Posted 21 September 2016 - 10:55 AM

I guess you know which route to take at least.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1