Drop index in SQL Server

Question

Can I drop all indexes of a schema in SQL Server according a condition?

I explain: I want drop all the indexes that contains a columns there types float, in schema XX.

I did this script below but I'm not sure of the line of the type of columns:

If Exists (Select * 
           From sys.indexes 
           Where name = 'indexName' 
             And Object_Id = Object_Id('schema xx.TABLE_NAME')
             And Object_Id =Object_Id ('nchar', 'nvarchar'))
    Drop Index indexNameOn dbo.Table_Name;

Thank you

Answer 1

This query, will help you to find all indexes where used columns with float datatype.

SELECT  i.[name] as IndexName,
        o.[name] as TableName
FROM sys.indexes i
INNER JOIN sys.objects o
    ON i.[object_id] = o.[object_id] AND o.[type] = 'U' --USER_TABLE
INNER JOIN sys.index_columns ic
    ON i.[object_id] = ic.[object_id]
INNER JOIN sys.columns c
    ON c.column_id = ic.column_id AND c.[object_id] = ic.[object_id]
INNER JOIN sys.types t
    ON  c.system_type_id = t.system_type_id AND t.[name] = 'float'

On MSDN you can find all info about this tables. To DROP all automatically and search in all DB's you need to use dynamic SQL.

This query works in current DB only, we need to search in each not-system DB.

DECLARE @query nvarchar(max) = N''

-- Create a table to store a results of a search
IF OBJECT_ID(N'tempdb..##search_index') IS NOT NULL DROP TABLE ##search_index
CREATE TABLE ##search_index (
    [db_name] sysname,
    [index_name] sysname,
    [schema_name] sysname,
    [table_name] sysname
)
-- here we get all DBs except system (master, tempdb etc.)
;WITH cte AS (
    SELECT  CONCAT(N'USE ',QUOTENAME([name]),';') as db,
            [name] as [db_name]
    FROM sys.databases      -- take all DBs
    WHERE database_id > 4   -- not system
)
-- prepare a query for each DB
SELECT @query = @query + db + CHAR(13) +
N'INSERT INTO ##search_index
SELECT  '''+ [db_name] + ''' as DB,
        i.[name] as IndexName,
        sch.[name] as SchemaName,
        o.[name] as TableName
FROM sys.indexes i
INNER JOIN sys.objects o
    ON i.[object_id] = o.[object_id] AND o.[type] = ''U'' -- USER_TABLE
INNER JOIN sys.schemas sch
    ON o.[schema_id] = sch.[schema_id]
INNER JOIN sys.index_columns ic
    ON i.[object_id] = ic.[object_id]
INNER JOIN sys.columns c
    ON c.column_id = ic.column_id AND c.[object_id] = ic.[object_id]
INNER JOIN sys.types t
    ON  c.system_type_id = t.system_type_id AND t.[name] = ''float'';' + CHAR(13) + CHAR(13)
FROM cte 

PRINT @query
-- execute query
EXEC(@query)

-- take a look at the results
SELECT *
FROM ##search_index

With the PRINT you can see what query was generated. The sample is here:

USE [Test];
INSERT INTO ##search_index
SELECT  'Test' as DB,
        i.[name] as IndexName,
        sch.[name] as SchemaName,
        o.[name] as TableName
FROM sys.indexes i
INNER JOIN sys.objects o
    ON i.[object_id] = o.[object_id] AND o.[type] = 'U' -- USER_TABLE
INNER JOIN sys.schemas sch
    ON o.[schema_id] = sch.[schema_id]
INNER JOIN sys.index_columns ic
    ON i.[object_id] = ic.[object_id]
INNER JOIN sys.columns c
    ON c.column_id = ic.column_id AND c.[object_id] = ic.[object_id]
INNER JOIN sys.types t
    ON  c.system_type_id = t.system_type_id AND t.[name] = 'float';

USE [Another One];
INSERT INTO ##search_index
SELECT  'Another One' as DB,
...

The output is like:

db_name         index_name  schema_name     table_name
Test            PK_Period   dbo             Periods
Another Test    PK_Test     dbo             Test

Then you need to check, if you want|need to delete this indexes. If you are sure about this you can use this:

DECLARE @query_to_drop nvarchar(max) = N''

SELECT @query_to_drop = @query_to_drop + N'USE ' + QUOTENAME([db_name]) +'; DROP INDEX ' + QUOTENAME([index_name]) + ' ON ' + QUOTENAME([schema_name]) +'.'+ QUOTENAME([table_name]) +';'+ CHAR(13)
FROM ##search_index

EXEC(@query_to_drop)

That will give you query like:

USE [Test]; DROP INDEX [PK_Period] ON [dbo].[Periods];
USE [Another Test]; DROP INDEX [PK_Test] ON [dbo].[Test];