当前位置 博文首页 > 时光下的旅途: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
?
cs