sqlserver批量删除字段 msrepl_tran_version
屁话不多说。
原因:
msrepl_tran_version由于有非空约束。所以不能直接删除。
--###############################################
--1.先删除约束:
--###############################################
DECLARE @SQL VARCHAR(999)
DECLARE CUR_CONSTRAINT CURSOR LOCAL FOR
SELECT
'ALTER TABLE '+
CASE WHEN O.schema_id IS NOT NULL THEN (SELECT NAME+'.' FROM sys.schemas WHERE schema_id = O.schema_id) ELSE '' END
+OBJECT_NAME(parent_object_id)+' DROP CONSTRAINT '+OBJECT_NAME(object_id)
FROM sys.objects AS O JOIN sys.schemas AS S on O.schema_id=S.schema_id
WHERE O.type IN('D') and o.name like 'msrepl_tran_version%'
OPEN CUR_CONSTRAINT
FETCH CUR_CONSTRAINT INTO @SQL
WHILE @@FETCH_STATUS =0
BEGIN
EXEC(@SQL)
FETCH CUR_CONSTRAINT INTO @SQL
END
CLOSE CUR_CONSTRAINT
DEALLOCATE CUR_CONSTRAINT
--###############################################
--2.删除多余字段:
--###############################################
DECLARE @table varchar(50)
DECLARE My_Cursor CURSOR --定义游标
FOR ( SELECT a.Name FROM SysObjects a inner join
syscolumns b on a.id = b.id
Where a.XType in ('U') and b.name='msrepl_tran_version' ) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @table; --读取第一行数据( )
WHILE @@FETCH_STATUS = 0
BEGIN
exec( 'alter table ' + @table + ' drop column msrepl_tran_version' )
FETCH NEXT FROM My_Cursor into @table; --读取下一行数据
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
GO