日期:2014-05-17 浏览次数:20499 次
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