locked
Dropping indexes from a database RRS feed

  • Question

  • SELECT

    schema_name(t.schema_id),t.name,  i.name,i.type_desc


    FROMsys.indexesi

    INNERJOINsys.tablest ONt.object_id=i.object_id


    --WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'


    --and (is_primary_key=0 and is_unique_constraint=0)


    orderbyi.name

    used this script to select 2179 indexes,

    DECLARE

    @SchemaName VARCHAR(256)DECLARE@TableName VARCHAR(256)


    DECLARE

    @IndexName VARCHAR(256)


    DECLARE

    @TSQLDropIndex VARCHAR(MAX)



    DECLARE

    CursorIndexes CURSORFOR


    SELECTschema_name(t.schema_id),t.name,  i.name

    FROMsys.indexesi

    INNERJOINsys.tablest ONt.object_id=i.object_id


    --WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'


    --and (is_primary_key=0 and is_unique_constraint=0)



    OPEN

    CursorIndexes


    FETCH

    NEXTFROMCursorIndexes INTO@SchemaName,@TableName,@IndexName



    WHILE

    @@fetch_status=0


    BEGIN


    SET@TSQLDropIndex ='DROP INDEX '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)+'.'+QUOTENAME(@IndexName)


    PRINT@TSQLDropIndex

    FETCHNEXTFROMCursorIndexes INTO@SchemaName,@TableName,@IndexName


    END



    CLOSE

    CursorIndexes


    DEALLOCATE

    CursorIndexes

    Will this delete all 2179 indexes? There are still indexes existing. What is the process?

    Friday, September 20, 2019 10:27 PM

Answers

  • Hi Avyayah,

    We are glad to hear that these comments are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Avyayah Thursday, September 26, 2019 1:10 PM
    Tuesday, September 24, 2019 7:07 AM

All replies

  • No, it will not (or, anyway, it won't drop everything that is in sysindexes).  The code is trying to drop anything that is in sysindexes.  But primary key and unique constraints have indexes.  You cannot drop those by DROP INDEX, you must drop them with DROP CONSTRAINT.  (It looks like whoever originally wrote the code knew this and included a WHERE clause that excluded primary key and unique constraints (among other things) but that code has been commented out.)

    Why would you want to drop every index in the database anyway?

    Tom

    Saturday, September 21, 2019 1:47 AM
  • You may refer:

    https://stackoverflow.com/questions/1344401/how-can-i-drop-all-indexes-in-a-sql-database-with-one-command


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    Saturday, September 21, 2019 3:32 AM
  • Hi

    Please find the below code which would fulfill your requirement.

    Drop Constraints (As Primary Key is generally associated to Clustered Index)

    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'';
    
    SELECT @sql = @sql + N'
      ALTER TABLE ' + QUOTENAME(s.name) + N'.'
      + QUOTENAME(t.name) + N' DROP CONSTRAINT '
      + QUOTENAME(c.name) + ';'
    FROM sys.objects AS c
    INNER JOIN sys.tables AS t
    ON c.parent_object_id = t.[object_id]
    INNER JOIN sys.schemas AS s 
    ON t.[schema_id] = s.[schema_id]
    WHERE c.[type] IN ('D','C','F','PK','UQ')
    ORDER BY c.[type];
    
    PRINT @sql;
    --EXEC sys.sp_executesql @sql;

    Drop all Indexes (Drops all other indexes)

    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

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Saturday, September 21, 2019 4:36 AM
  • Hi Avyayah,

    Why do you want to drop all the indexes? Is that because you want to truncate all the database? 

    (1) if you want to drop all the indexes : 

    ---for foreign keys 
    while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
    begin
    declare @sql nvarchar(2000)
    SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
    	+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM information_schema.table_constraints
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    exec (@sql)
    end
    
    -- FOR OTHER CONSTRAINTS 
    declare @str varchar(max)
    declare cur cursor for
    SELECT 'ALTER TABLE ' + '[' + s.[NAME] + '].[' + t.name + '] DROP CONSTRAINT ['+ c.name + ']'
    FROM sys.objects c, sys.objects t, sys.schemas s
    WHERE c.type IN ('C', 'F', 'PK', 'UQ', 'D')
     AND c.parent_object_id=t.object_id and t.type='U' AND t.SCHEMA_ID = s.schema_id
    ORDER BY c.type
    open cur
    FETCH NEXT FROM cur INTO @str
    WHILE (@@fetch_status = 0) BEGIN
     PRINT @str
     EXEC (@str)
     FETCH NEXT FROM cur INTO @str
    END
    close cur
    deallocate cur

    (2) if you want to truncate the database: 

    Hope it could give you some ideas.

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 6:22 AM
  • select *fromsys.indexes generates outputs 2737 indexes

    while

    SELECTschema_name(t.schema_id),t.name,  i.name,i.type_desc

    FROMsys.indexesi

    INNERJOINsys.tablest ONt.object_id=i.object_id


    WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'


    and (is_primary_key=0 and is_unique_constraint=0)


    orderbyi.name

    generated 1065.

    Which is the correct query to select all indexes from the databases. We are having issues while upgrading an application and the vendor has asked to delete all indexes from the database and recreating before upgrade.

    Please send me the query to the name and total count of indexes delete from the databases.

    Monday, September 23, 2019 2:39 PM
  • During upgrade we are having issues with an application and the vendor is asking us to delete all indexes in the database and recreate what is needed. I want to select first name of all indexes and then drop so that I know how many and what indexes are dropped.
    Monday, September 23, 2019 2:45 PM
  • Dropping all indexes is not a solution to anything.

    What exactly is the problem you are experiencing?

    Monday, September 23, 2019 3:40 PM
  • Since this is a request that came from the vendor I tried this script to delete 653 indexes and waiting to hear from the application team. Yes, I agree that dropping all the indexes is not the solution but the team does not know of any other solution and since it complains about the indexes it is best to drop the indexes. I am waiting for responses and will be asking for suggestion if there are further issues.

    DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256)

    DECLARE @IndexName VARCHAR(256)

    DECLARE @TSQLDropIndex VARCHAR(MAX)

    DECLARE CursorIndexes CURSOR FOR

     SELECT schema_name(t.schema_id), t.name,  i.name

     FROM sys.indexes i

     INNER JOIN sys.tables t ON t.object_id= i.object_id

     WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'

     and (is_primary_key=0 and is_unique_constraint=0)

    OPEN CursorIndexes

    FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName

    WHILE @@fetch_status = 0

    BEGIN

     SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)

     PRINT @TSQLDropIndex

     FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName

    END

    CLOSE CursorIndexes

    DEALLOCATE CursorIndexes

    Monday, September 23, 2019 4:13 PM
  • What do you mean "since it complains about the indexes"?  Are you getting an error message of some kind?
    Monday, September 23, 2019 5:03 PM
  • Yes, during upgrade there were errors from the application and about 20 plus indexes had to be dropped manually. So vendor suggested on dropping all indexes. Application team is working on the upgrade and waiting for responses.
    Monday, September 23, 2019 5:34 PM
  • Again what does "during upgrade there were errors from the application and about 20 plus indexes had to be dropped manually" mean?

    Indexes should not cause any errors, unless something is checking for additional indexes and/or the name is duplicated.

    Are you saying YOUR COMPANY added indexes to a vendor database, which the vendor did not approve?  


    Monday, September 23, 2019 6:06 PM
  • Thank you for all the great comments and suggestions. I am waiting on the application team now. This is an old Team Center application and not ones knows if there were indexes that were applied by our Company.

    Indexes should not cause any errors, unless something is checking for additional indexes and/or the name is duplicated---This comment is a great suggestion will keep that in mind when I hear back responses from the vendor.

    Monday, September 23, 2019 6:37 PM
  • Indexes should not cause any errors, unless something is checking for additional indexes and/or the name is duplicated

    That's not entirely correct.  If you update alters the datatype of a column that is either a key or included in an index, then you might (or might not) get an error depending on what type of change you are making to the datatype. 

    For example, if you change an int to a varchar or a varchar(20) to a varchar(10), you would get an error if that column was in an index.  But if the change was varchar(20) to a varchar(40), that would not give an error.

    Tom

    Monday, September 23, 2019 7:10 PM
  • Thank you Tom for sharing insite on database indexes and their function on the application. I will remember this as well when I discuss with vendor.
    Monday, September 23, 2019 8:05 PM
  • Hi Avyayah,

    We are glad to hear that these comments are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Avyayah Thursday, September 26, 2019 1:10 PM
    Tuesday, September 24, 2019 7:07 AM