SQL Server数据库工具类存储过程

1、根据表名和字段名，删除该字段的外键引用

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'SP_T_DROP_FK')
  BEGIN
    DROP PROCEDURE [dbo].SP_T_DROP_FK
  END
GO

CREATE PROCEDURE SP_T_DROP_FK
      @table_name VARCHAR(100) ,	--表名
      @column_name VARCHAR(100)		--字段名
AS
      BEGIN
	--该存储过程的作用是根据表名和字段名，删除该字段的外键引用
            SET NOCOUNT ON;
            DECLARE @SQL VARCHAR(MAX) = '';

            SELECT
                @SQL = @SQL + 'ALTER TABLE [dbo].[' + ptabs.name + '] DROP CONSTRAINT [' + fk.name + '];'
            FROM
                sys.foreign_keys fk
            JOIN sys.foreign_key_columns fkcols ON fk.object_id = fkcols.constraint_object_id
            JOIN sys.columns rcols ON rcols.object_id = fkcols.referenced_object_id AND rcols.column_id = fkcols.referenced_column_id
            JOIN sys.tables rtabs ON fk.referenced_object_id = rtabs.object_id AND rtabs.type = 'U'
            JOIN sys.columns pcols ON pcols.object_id = fkcols.parent_object_id AND pcols.column_id = fkcols.parent_column_id
            JOIN sys.tables ptabs ON fk.parent_object_id = ptabs.object_id AND ptabs.type = 'U'
            WHERE
                ptabs.name = @table_name
                AND pcols.name = @column_name;

			EXEC(@SQL);
            SET NOCOUNT OFF;
      END;
GO

 

2、根据表名和字段名，删除包含该字段的索引(包括唯一索引)

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'SP_T_DROP_INDEX')
  BEGIN
    DROP PROCEDURE [dbo].SP_T_DROP_INDEX
  END
GO

CREATE PROCEDURE SP_T_DROP_INDEX
      @table_name VARCHAR(100) ,	--表名
      @column_name VARCHAR(100)		--字段名
AS
      BEGIN
	--该存储过程的作用是根据表名和字段名，删除包含该字段的索引(包括唯一索引)
            SET NOCOUNT ON;
            DECLARE @SQL VARCHAR(MAX) = '';

            SELECT
				@SQL = @SQL + CASE idx.is_unique_constraint WHEN 1 THEN 'ALTER TABLE [' + t.name + '] DROP CONSTRAINT [' + idx.name + '];'
				ELSE 'DROP INDEX [' + idx.name + '] ON [' + t.name + '];' END
            FROM
                sys.indexes idx
            JOIN sys.tables t ON t.object_id = idx.object_id AND t.type = 'U'
            JOIN sys.index_columns idc ON idc.object_id = t.object_id AND idc.index_id = idx.index_id
            JOIN sys.columns col ON t.object_id = col.object_id AND col.column_id = idc.column_id
            WHERE
                t.name = @table_name
                AND col.name = @column_name;

            EXEC(@SQL);
            SET NOCOUNT OFF;
      END;

GO

 

3、根据表名和字段名，删除包含该字段的约束

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'SP_T_DROP_CONSTRAINT')
  BEGIN
    DROP PROCEDURE [dbo].SP_T_DROP_CONSTRAINT
  END
GO

CREATE PROCEDURE SP_T_DROP_CONSTRAINT
      @table_name VARCHAR(100) ,	--表名
      @column_name VARCHAR(100)		--字段名
AS
      BEGIN
	--该存储过程的作用是根据表名和字段名，删除包含该字段的约束
            SET NOCOUNT ON;
            DECLARE @SQL VARCHAR(MAX) = '';

            SELECT
                @SQL = @SQL + 'ALTER TABLE [' + tb.name + '] DROP CONSTRAINT [' + dc.name + '];'
            FROM
                sys.default_constraints dc
            JOIN sys.tables tb ON dc.parent_object_id = tb.object_id
            JOIN sys.columns col ON dc.parent_object_id = col.object_id AND dc.parent_column_id = col.column_id
            WHERE
                tb.name = @table_name
                AND col.name = @column_name;

            EXEC(@SQL);
            SET NOCOUNT OFF;
      END;

GO

 

4、根据表名和字段名，删除该表的当前字段

IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'SP_T_DROP_COL')
  BEGIN
    DROP PROCEDURE [dbo].SP_T_DROP_COL
  END
GO

CREATE PROCEDURE [dbo].[SP_T_DROP_COL]
      @table_name VARCHAR(100) ,	--表名
      @column_name VARCHAR(100)		--字段名
AS
      BEGIN
		--该存储过程的作用是根据表名和字段名，删除该表的当前字段
            SET NOCOUNT ON;

      EXEC SP_T_DROP_FK @table_name = @table_name,  @column_name = @column_name
			EXEC SP_T_DROP_INDEX @table_name = @table_name,  @column_name = @column_name
			EXEC SP_T_DROP_CONSTRAINT @table_name = @table_name,  @column_name = @column_name

			DECLARE @SQL VARCHAR(MAX) = 'IF EXISTS (SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID(''' + @table_name + ''') AND NAME = ''' + @column_name + ''') ' + CHAR(10) + CHAR(13)
			SET @SQL = @SQL + ' BEGIN ' + CHAR(10) + CHAR(13)
			SET @SQL = @SQL + ' ALTER TABLE ' + @table_name + ' DROP COLUMN ' + @column_name + ';' + CHAR(10) + CHAR(13)
			SET @SQL = @SQL + ' END ' + CHAR(10) + CHAR(13)

			EXEC(@SQL)

            SET NOCOUNT OFF;
      END;
GO