34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
SELECT @SQL=@SQL+'ALTER TABLE ['+T4.name+'] DROP CONSTRAINT ['+T1.name+'] '
FROM
SYS.SYSINDEXES T1 JOIN SYS.SYSINDEXKEYS T2 ON T1.id=T2.id AND T1.indid=T2.indid
JOIN SYS.SYSCOLUMNS T3 ON T1.id=T3.id AND T2.colid=T3.colid
JOIN SYS.SYSOBJECTS T4 ON T1.id=T4.id
WHERE T3.name='_mask_from_v2'
EXEC(@SQL)
SET @SQL=''
SELECT @SQL=@SQL+'ALTER TABLE ['+T1.name+'] DROP COLUMN ['+T2.name+'] '
FROM SYS.SYSOBJECTS T1 JOIN SYS.SYSCOLUMNS T2 ON T1.id=T2.id AND T2.name='_mask_from_v2'
EXEC(@SQL)
试下,应该可以,先在测试数据库,建个测试表,取个列名_mask_from_v2,并加个索引试下DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
SELECT @SQL=@SQL+'ALTER TABLE ['+T1.name+'] DROP COLUMN ['+T2.name+'] '
FROM SYS.SYSOBJECTS T1 JOIN SYS.SYSCOLUMNS T2 ON T1.id=T2.id AND T2.name='_MASK_TO_V2'
EXEC(@SQL)
你得想好了才能执行上面的语句
另外,有没有问下客户,为啥会有呢?是客户加的吗?还有,你们程序是怎么处理XML的,多了一列就不行了?难不成按数字索引读取XML的?USE 数据库名
GO
DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
SELECT @SQL=@SQL+'ALTER TABLE ['+T1.name+'] DROP COLUMN ['+T2.name+'] '
FROM SYS.SYSOBJECTS T1 JOIN SYS.SYSCOLUMNS T2 ON T1.id=T2.id AND T2.name='_mask_from_v2'
EXEC(@SQL)
你先想好了,万一反悔,这步的逆操作就只有还原数据库最方便了