如果是要删除两张表中的呢?这两张表指定的字段一样。比如都保留 A B C这三个字段。删除其余的所有字段。sql
解决方案 »
- sql语句不能显示完全
- sql数据库发布时,其中的表的最大列数不能比255大?有什么解决方法没有?
- group by 问题
- 有表结构和测试数据,怎么写SQL统计语句,比较复杂
- sqlserver能负载平衡吗?
- [odbc sql server driver]超时过期
- 今天想在WIN2000+SP3上安装一套SQLSERVER 6.5失败了......
- 请教,帮忙!关于"PRIMAR"已满
- 不用动态sql,不用循环写这个存储过程
- 我用delphi做的前台,后台用SQL server 2000.结果报错了:Microsoft][ODBC SQL Server Driver][SQL Server]在此上下文中不允许使用'DDD'
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,
[Re] [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,Re=null,OrderNO=null,Flag=null,RelateID=null,ParentID=null,OpID=null,OpDate=null,CraftType=null*/
if OBJECT_ID('tempdb..b') is not null drop table tempdb..bselect 1 A,2 B,3 C, 4 D into tempdb..a
select 1 A,2 B,3 C, 4 D ,5 E ,6 F into tempdb..b
select * from tempdb..a
select * from tempdb..bdeclare @sql varchar(max)
set @sql = ''
select @sql = @sql + ' alter table tempdb..a drop column ['+b.name+']'
from tempdb..sysobjects a join tempdb..syscolumns b on b.id=a.id
where a.name = 'a' and a.xtype = 'U'
and b.name not in ('A','B','C')
print @sql
exec(@sql)set @sql = ''
select @sql = @sql + ' alter table tempdb..b drop column ['+b.name+']'
from tempdb..sysobjects a join tempdb..syscolumns b on b.id=a.id
where a.name = 'b' and a.xtype = 'U'
and b.name not in ('A','B','C')
print @sql
exec(@sql)select * from tempdb..a
select * from tempdb..b
if OBJECT_ID('tempdb..b') is not null drop table tempdb..bselect 1 A,2 B,3 C, 4 D into tempdb..a
select 1 A,2 B,3 C, 4 D ,5 E ,6 F into tempdb..b
select * from tempdb..a
select * from tempdb..bdeclare @sql varchar(max)
set @sql = ''
select @sql = @sql + ' alter table tempdb..a drop column ['+b.name+']'
from tempdb..sysobjects a join tempdb..syscolumns b on b.id=a.id
where a.name = 'a' and a.xtype = 'U'
and b.name not in ('A','B','C')
print @sql
exec(@sql)set @sql = ''
select @sql = @sql + ' alter table tempdb..b drop column ['+b.name+']'
from tempdb..sysobjects a join tempdb..syscolumns b on b.id=a.id
where a.name = 'b' and a.xtype = 'U'
and b.name not in ('A','B','C')
print @sql
exec(@sql)select * from tempdb..a
select * from tempdb..b
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'
) /*
tablename colname delsql
test3 bb alter table test3 drop column [bb]
test3 cc alter table test3 drop column [cc]
*/
SET @sql = N'
alter table test3 drop column
'+STUFF((
SELECT ','+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'
)
FOR XML PATH('')),1,1,'')
EXEC(@sql)