declare tb cursor for
SELECT sql='alter table ['+d.name
+'] alter column ['+a.name+'] varchar(50)
update ['+d.name+'] set ['+a.name+']=rtrim(['+a.name+'])'
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where
b.name='char' and a.prec<50
and
not exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) --主键不能修改
order by d.name,a.name
declare @sql varchar(1000)
open tb
fetch next from tb into @sql
while @@fetch_status = 0
begin
exec(@sql)
fetch next from tb into @sql
end
close tb
deallocate tb
SELECT sql='alter table ['+d.name
+'] alter column ['+a.name+'] varchar(50)
update ['+d.name+'] set ['+a.name+']=rtrim(['+a.name+'])'
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where
b.name='char' and a.prec<50
and
not exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) --主键不能修改
order by d.name,a.name
declare @sql varchar(1000)
open tb
fetch next from tb into @sql
while @@fetch_status = 0
begin
exec(@sql)
fetch next from tb into @sql
end
close tb
deallocate tb
@TableName Varchar(50),
@ColName Varchar(50)
As
Begin
If Exists (Select * from SysColumns Where ID=OBJECT_ID(@TableName) And Name =@ColName)
Begin
Declare @Len Int
Declare @Sql Varchar(8000)
Select @Len=Length from SysColumns Where ID=OBJECT_ID(@TableName) And Name =@ColName
If @Len<=50
Set @Len=50
Set @Sql='Alter Table '+@TableName +' Alter Column '+@ColName +' Nvarchar('+Rtrim(@Len)+')'
EXEC(@Sql)
Set @Sql='Update '+@TableName +' Set '+@ColName+' =Rtrim('+@ColName+')'
EXEC(@Sql)
End
End
邹老大出错是:对象'DF_dbfadcliao_danhao' 依赖于 列 'addhao'
索引'idxaddhao'依赖于列 'addhao'
alter table alter column addhao 失败,因为有一个或多个对象访问此列。鱼你的也出错:对象'DF_dbfadcliao_sehao' 依赖于 列 'sehao'
alter table alter column sehao 失败,因为有一个或多个对象访问此列.
Create table TEST
(Name char(10))
--插入数据
Insert TEST Values('AA')
Insert TEST Values('BBBB')
GO
--建立存储过程
Create Procedure AlterColumn
@TableName Varchar(50),
@ColName Varchar(50)
As
Begin
If Exists (Select * from SysColumns Where ID=OBJECT_ID(@TableName) And Name =@ColName)
Begin
Declare @Len Int
Declare @Sql Varchar(8000)
Select @Len=Length from SysColumns Where ID=OBJECT_ID(@TableName) And Name =@ColName
If @Len<=50
Set @Len=50
Set @Sql='Alter Table '+@TableName +' Alter Column '+@ColName +' Nvarchar('+Rtrim(@Len)+')'
EXEC(@Sql)
Set @Sql='Update '+@TableName +' Set '+@ColName+' =Rtrim('+@ColName+')'
EXEC(@Sql)
End
End
GO
--测试
Select * from TEST
Select A.Name As ColName,B.Name As Type ,A.Length As 占用字节数 from SysColumns A Inner Join SysTypes B On A.Xusertype=B.Xusertype Where A.ID=OBJECT_ID('TEST') And A.Name ='Name'
EXEC AlterColumn 'TEST','Name'
GO
Select * from TEST
Select A.Name As ColName,B.Name As Type ,A.Length As 占用字节数 from SysColumns A Inner Join SysTypes B On A.Xusertype=B.Xusertype Where A.ID=OBJECT_ID('TEST') And A.Name ='Name'
--删除测试环境
Drop Table TEST
Drop Procedure AlterColumn
--结果
/*
--执行前
Name
AA
BBBB
ColName Type Length
Name char 10--执行后
Name
AA
BBBB
ColName Type Length --注明一下:Nvarchar占两个字节,所以是100,在企业管理器中可以查询到长度为50
Name nvarchar 100
*/
没有关联呀
只是有此索引有可能是包含两个字段。
索引'idxaddhao'依赖于列 'addhao'这个错误是addhao列上有索引和默认值约束
ALTER COLUMN指定要更改给定列。如果兼容级别是 65 或小于 65,将不允许使用 ALTER COLUMN。有关更多信息,请参见 sp_dbcmptlevel。 要更改的列不能是: 数据类型为 text、image、ntext 或 timestamp 的列。
表的 ROWGUIDCOL 列。
计算列或用于计算列中的列。
被复制列。
用在索引中的列,除非该列数据类型是 varchar、nvarchar 或 varbinary,数据类型没有更改,而且新列大小等于或者大于旧列大小。
用在由 CREATE STATISTICS 语句创建的统计中的列。首先用 DROP STATISTICS 语句删除统计。由查询优化器自动生成的统计会由 ALTER COLUMN 自动除去。
用在 PRIMARY KEY 或 [FOREIGN KEY] REFERENCES 约束中的列。
用在 CHECK 或 UNIQUE 约束中的列,除非用在 CHECK 或 UNIQUE 约束中的可变长度列的长度允许更改。
有相关联的默认值的列,除非在不更改数据类型的情况下允许更改列的长度、精度或小数位数。
有些数据类型的更改可能导致数据的更改。例如,将数据类型为 nchar 或 nvarchar 的列更改为 char 或 varchar 类型,将导致扩展字符的转换。有关更多信息,请参见 CAST 和 CONVERT。降低列的精度和小数位数可能导致数据截断。
@tablename nvarchar(20)
as
declare @columnname nvarchar(20)
,@columnlength smallintdeclare c_table cursor local
for
select syscolumns.name,syscolumns.length
from syscolumns left join sysobjects
on syscolumns.id=sysobjects.id
left join systypes
on syscolumns.xtype=systypes.xusertype
where sysobjects.id=object_id(@tablename)
and
systypes.name in ('char','varchar')
and
not exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = syscolumns.id AND colid=syscolumns.colid)))
for read onlyopen c_table
fetch next from c_table into @columnname,@columnlength
while @@fetch_status>=0
begin
if (@columnlength>50)
begin
exec('alter table '+@tablename+' alter column '+@columnname+' nvarchar'+'('+@columnlength+')')
end
else
begin
exec('alter table '+@tablename+' alter column '+@columnname+' nvarchar(50)')
end
fetch next from c_table into @columnname,@columnlength
end
close c_table
deallocate c_table
go
在邹老大的基础上做了些改动,加入了参数,和当字段长度
你那个根本就不行,因为有些列有默认约束,所以你那样做会出错,除非先删除约束(删除前保存)
to 邹老大
你的思路不错。不过我要一个表里所有字段为char型 改为varchar的存储过程。并当长度小于50时,就默认为50,大于就不变。记得一定要先删除约束,再建约束
盼回!!!!!!!!!!!!!!!!!!!!!!!!!!!!
同时也要谢谢solider2005(菜鸟)
chichunhua(無愧與心) 你也是好人,
这分怎么给呀!交给版主