alter table opcard alter column docDoStatus,shipmentStatus,opspecial nvarchar(300) null我是这样写的
可以通过syscolumns找到字段,然后组合SQL,但是那还不如直接修改来的简单。 alter table opcard alter column docDoStatus nvarchar(300) nullalter table opcard alter column shipmentStatus nvarchar(300) nullalter table opcard alter column opspecial nvarchar(300) null必须要一条一条的写
有几千条呢~~~也要一条一条写吗?有人说存储过程做的话会容易很多...很迷茫ING
--修改一个表中所用列的数据类型为nvarchar(300) create proc AAA @tablename varchar(100) AS declare @count int,@colname varchar(100),@sql varchar(8000) create table #aa(id int identity(1,1),name varchar(100)) insert #aa(name) select b.name from sysobjects a join syscolumns b on a.id=b.id and a.name=@tablename set @count=@@identitywhile @count>0 begin select @colname=name from #aa where id=@count set @sql='alter table '+@tablename+ ' alter column '+@colname+' nvarchar(300) null' select @sql exec (@sql) set @count=@count-1 end Go exec AAA 'Yourtable'
--修改一个表中所用列的数据类型为nvarchar型,长度不变,是否为空不变 create proc sp_changedatatype ( @tablename varchar(100), --表名 @oldtype varchar(100), --原来的数据类型 @newtype varchar(100) --新的数据类型 ) as declare @count int, @colname varchar(100), @coltype varchar(100), @collength int, @colisnull varchar(100), @sql varchar(8000) create table #Tp(id int identity(1,1), name varchar(100), type varchar(100), length int, isnullable varchar(100)) insert #Tp(name, type, length, isnullable) select column_name as 列名,data_type as 类型,character_maximum_length as 长度, is_nullable as 为空 from information_schema.columns where table_name=@tablename and data_type=@oldtype set @count=@@identity set nocount on while @count>0 begin select @colname=name, @coltype=type, @collength=length, @colisnull= isnullable from #Tp where id=@count if @colisnull='yes' set @sql='alter table '+@tablename+' alter column '+@colname+' ' + @newtype + ' (' + cast(@collength as varchar(20)) + ') null' else set @sql='alter table '+@tablename+' alter column '+@colname+ ' ' + @newtype + ' (' + cast(@collength as varchar(20)) + ') not null' if @sql is not null begin select @sql exec (@sql) end set @count=@count-1 end go --调用示例 --循环每个用户表declare cur cursor read_only for select name from sysobjects a where A.xtype='U' and a.name<>'dtproperties'declare @TableName varchar(40) open curfetch next from cur into @TableName while (@@fetch_status =0) begin exec sp_ChangeDataType @TableName,'nvarchar','varchar' fetch next from cur into @TableName endclose cur deallocate cur
alter column docDoStatus,shipmentStatus,opspecial nvarchar(300) null我是这样写的
alter table opcard
alter column docDoStatus nvarchar(300) nullalter table opcard
alter column shipmentStatus nvarchar(300) nullalter table opcard
alter column opspecial nvarchar(300) null必须要一条一条的写
create proc AAA
@tablename varchar(100)
AS
declare @count int,@colname varchar(100),@sql varchar(8000)
create table #aa(id int identity(1,1),name varchar(100))
insert #aa(name)
select b.name
from sysobjects a join syscolumns b on a.id=b.id and a.name=@tablename
set @count=@@identitywhile @count>0
begin
select @colname=name from #aa where id=@count
set @sql='alter table '+@tablename+
' alter column '+@colname+' nvarchar(300) null'
select @sql
exec (@sql)
set @count=@count-1
end
Go
exec AAA 'Yourtable'
[PreOrderID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UnionID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[petd] [datetime] NULL ,
[suppliercode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[supplierTEXT] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[SHIPPTEXT] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[cneecode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cneeTEXT] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[dagtTEXT] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[coloaderTEXT] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[stocktext] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[CtmAgtoptext] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[tpcs] [int] NULL ,
[dateCargoInP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[dateCargoInT] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CargoInStatus] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[dateDocInP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[docTodate] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[docDoStatus] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[CustomStatus] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[dateCustom] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[customResult] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[shipmentStatus] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[opspecial] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[IdAuto] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[sysDate] [datetime] NULL ,
[mkCntMethod] [int] NULL ,
[CustomMethod] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ETA] [datetime] NULL ,
[StockInDate] [datetime] NULL ,
[StockOutDate] [datetime] NULL ,
[StockInStatus] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[SubManifestFrm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MerchantStyle] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[TaxStyle] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CargoAttr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]如果依照这样一个表修改~把里面的所有varchar类型的字段变成nvarchar,而且长度是不变的……昨天想到半夜也没想出来。。急啊
--修改一个表中所用列的数据类型为nvarchar型,长度不变,是否为空不变
create proc sp_changedatatype
(
@tablename varchar(100), --表名
@oldtype varchar(100), --原来的数据类型
@newtype varchar(100) --新的数据类型
)
as
declare @count int,
@colname varchar(100),
@coltype varchar(100),
@collength int,
@colisnull varchar(100),
@sql varchar(8000) create table #Tp(id int identity(1,1), name varchar(100), type varchar(100), length int, isnullable varchar(100)) insert #Tp(name, type, length, isnullable)
select column_name as 列名,data_type as 类型,character_maximum_length as 长度, is_nullable as 为空
from information_schema.columns
where table_name=@tablename and data_type=@oldtype set @count=@@identity set nocount on
while @count>0
begin
select @colname=name, @coltype=type, @collength=length, @colisnull= isnullable from #Tp where id=@count if @colisnull='yes'
set @sql='alter table '+@tablename+' alter column '+@colname+' ' + @newtype + ' (' + cast(@collength as varchar(20)) + ') null'
else
set @sql='alter table '+@tablename+' alter column '+@colname+ ' ' + @newtype + ' (' + cast(@collength as varchar(20)) + ') not null' if @sql is not null
begin
select @sql
exec (@sql)
end set @count=@count-1
end
go
--调用示例
--循环每个用户表declare cur cursor
read_only
for select name
from sysobjects a
where A.xtype='U' and a.name<>'dtproperties'declare @TableName varchar(40)
open curfetch next from cur into @TableName
while (@@fetch_status =0)
begin
exec sp_ChangeDataType @TableName,'nvarchar','varchar' fetch next from cur into @TableName
endclose cur
deallocate cur