sql怎么样删除表中指定字段以外的所有字段？
如果是要删除两张表中的呢？
这两张表指定的字段一样。比如都保留 A B C这三个字段。删除其余的所有字段。 sql
[解决办法]
给你一个脚本可以生成删除代码，下面是例子
CREATE TABLE [dbo].[PP_CraftData](
[CraftDataID] [int] IDENTITY(1,1) NOT NULL,
[DataPlatID] [int] NULL,
[CraftID] [int] NULL,
[ProcessID] [int] NULL,
[GoodsID] [int] NULL,
[ProductID] [int] NULL,
[ProductCode] [varchar](60) COLLATE Chinese_PRC_CI_AS NULL,
[Machine] [int] NULL,
[StandardValue] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[Remark] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[OrderNO] [int] NULL,
[Flag] [char](1) COLLATE Chinese_PRC_CI_AS NULL,
[RelateID] [int] NULL,
[ParentID] [int] NULL,
[OpID] [int] NULL,
[OpDate] [datetime] NULL,
[CraftType] [int] NULL,
CONSTRAINT [PK_PP_CraftData] PRIMARY KEY CLUSTERED
(
[CraftDataID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
declare @s varchar(max)
set @s='update PP_CraftData set '
select @s=@s+''+name+'=null,'
from syscolumns
where id=object_id('PP_CraftData') and name<>'指定字段'
order by colid
print left(@s,len(@s)-1)
结果：
/*
update PP_CraftData set CraftDataID=null,DataPlatID=null,CraftID=null,ProcessID=null,GoodsID=null,ProductID=null,ProductCode=null,Machine=null,StandardValue=null,Remark=null,OrderNO=null,Flag=null,RelateID=null,ParentID=null,OpID=null,OpDate=null,CraftType=null
*/
SELECT
tablename = a.NAME,
colname = b.name,
delsql = 'alter table test3 drop column '+ QUOTENAME(b.NAME)
FROM sys.tables a
INNER JOIN sys.columns b
ON a.object_id = b.object_id
WHERE a.name = 'test3'
AND b.name NOT IN
(
'a',
'b',
'c'
)
/*
tablenamecolnamedelsql
test3bbalter table test3 drop column [bb]
test3ccalter table test3 drop column [cc]
*/
