sql-server - 在 SQL Server 中删除索引

Question

我可以根据条件删除 SQL Server 中架构的所有索引吗？

我解释一下:我想删除 schema XX 中包含 float 类型列的所有索引。

我在下面做了这个脚本，但我不确定列类型的行:

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;

谢谢

Answer 1

此查询将帮助您查找使用 float 据类型列的所有索引。

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'

在 MSDN 上您可以找到有关此表的所有信息。要自动删除所有内容并在所有数据库中搜索，您需要使用动态 SQL。

此查询仅在当前数据库中有效，我们需要在每个非系统数据库中搜索。

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

通过 PRINT 您可以看到生成了什么查询。示例在这里:

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,
...

输出如下:

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

然后您需要检查是否需要删除该索引。如果你确定这一点，你可以使用这个:

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)

这会给你这样的查询:

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