关于如何完全删除一个表字段的方法，包括索引、约束

           IF EXISTS ( SELECT TOP 1
                            1
                    FROM    INFORMATION_SCHEMA.COLUMNS
                    WHERE   [TABLE_NAME] = 'ShopSkuId'
                            AND [COLUMN_NAME] = 'ProductBatch' )
            BEGIN
                 --根据字段删除索引
                DECLARE @TableName NVARCHAR(50)= 'ProductBatch'
                DECLARE @RowName NVARCHAR(50)= 'ShopSkuId'
                DECLARE @sql NVARCHAR(500)
                IF EXISTS ( SELECT  indexname = a.name  --索引名称
                                    ,
                                    tablename = c.name  --表名称
                                    ,
                                    indexcolumns = d.name --字段名称
                                    ,
                                    a.indid
                            FROM    sysindexes a
                                    JOIN sysindexkeys b ON a.id = b.id
                                                           AND a.indid = b.indid
                                    JOIN sysobjects c ON b.id = c.id
                                    JOIN syscolumns d ON b.id = d.id
                                                         AND b.colid = d.colid
                            WHERE   a.indid NOT IN ( 0, 255 )
                                    AND c.name = @TableName
                                    AND d.name = @RowName )
                    BEGIN
                        DECLARE @index_name NVARCHAR(50)
      
                        SELECT  @index_name = a.name
                        FROM    sysindexes a
                                JOIN sysindexkeys b ON a.id = b.id
                                                       AND a.indid = b.indid
                                JOIN sysobjects c ON b.id = c.id
                                JOIN syscolumns d ON b.id = d.id
                                                     AND b.colid = d.colid
                        WHERE   a.indid NOT IN ( 0, 255 )
                                AND c.name = @TableName
                                AND d.name = @RowName
        
                        SET @sql = 'drop INDEX ' + @index_name + ' on '
                            + @TableName;
                        EXEC(@sql)
                    END
                -- 删除约束
                DECLARE @defname VARCHAR(100) 
                DECLARE @cmd VARCHAR(100) 
                SELECT  @defname = name
                FROM    sysobjects so
                        JOIN sysconstraints sc ON so.id = sc.constid
                WHERE   OBJECT_NAME(so.parent_obj) = @tablename
                        AND so.xtype = 'D'
                        AND sc.colid = ( SELECT colid
                                         FROM   syscolumns
                                         WHERE  id = OBJECT_ID(@tablename)
                                                AND name = @RowName
                                       ) 
                SELECT  @cmd = 'ALTER TABLE ' + @tablename
                        + ' DROP CONSTRAINT ' + @defname 
                IF @cmd IS NOT NULL
                    EXEC  ( @cmd )
            -- 删除字段
                SET @sql = 'ALTER TABLE ' + @TableName + ' DROP COLUMN '
                    + @RowName
                EXEC  ( @sql )
            END

 

 

 

　　

转载于:https://www.cnblogs.com/lhll/p/9815093.html