create procedure p_set as declare tb cursor for SELECT sql='alter table ['+d.name +'] alter column ['+a.name+'] var' +b.name+'('+cast(a.length*2 as varchar)+')' 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 in('char') 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) print @sql fetch next from tb into @sql end close tb deallocate tb go exec P_set
select 'alter table '+b.name+' alter column '+a.name+' varchar(100)' from syscolumns a,sysobjects b where a.id=b.id and a.xtype in(175,239) and b.xtype='U' 把上面语句的到的结果贴出来,再运行
select 'alter table '+b.name+' alter column '+a.name+' varchar('+cast(a.length as varchar)+')' from syscolumns a inner join sysobjects b on a.id=b.id where a.xtype=175a.xtype=175表示数据类型为char varchar的a.xtype=167
/* 引用,邹建老大的代码! */ CREATE PROC P_CharToVarchar @type tinyint=0 --修改方式,0=仅查询可修改情况,1=仅所有列可修改时才修改,2=修改可修改列,报告不可修改列 AS SET NOCOUNT ON SELECT TableName=o.name,FieldName=c.name, FieldType=t.name+N'('+CAST(c.prec as varchar)+N')' +CASE WHEN c.isnullable=1 THEN N'' ELSE N' NOT' END +N' NULL', FieldLen=CONVERT(VARCHAR,c.prec), NoChangeCause=CAST(STUFF( CASE WHEN COLUMNPROPERTY(c.id,c.name,N'IsComputed')=1 THEN N',计算列' ELSE N'' END +CASE WHEN c.cdefault=0 THEN N'' ELSE N',列具有默认值' END +CASE WHEN EXISTS( SELECT * FROM sysindexkeys idxk,sysindexes idx WHERE idxk.id=c.id AND idxk.colid=c.colid AND idxk.id=idx.id AND idxk.indid=idx.indid AND idx.indid NOT IN(0,255) AND INDEXPROPERTY(idx.id,idx.name,N'IsAutoStatistics')=0) THEN N',列被主键、唯一键、索引、STATISTICS引用' ELSE N'' END +CASE WHEN EXISTS( SELECT * FROM sysforeignkeys WHERE fkeyid=c.id AND fkey=c.colid) THEN N',列被外键约束引用' ELSE N'' END +CASE WHEN EXISTS( SELECT * FROM sysobjects oc,sysdepends d WHERE oc.parent_obj=o.id AND OBJECTPROPERTY(oc.id,N'IsCheckCnst')=1 AND d.id=oc.id AND d.depnumber=c.colid) THEN N',列被CHECK约束引用' ELSE N'' END,1,1,N'') as nvarchar(4000)) INTO # FROM sysobjects o,syscolumns c,systypes t WHERE o.id=c.id and o.status>=0 AND OBJECTPROPERTY(o.id,N'IsUserTable')=1 AND t.xusertype=c.xusertype AND t.name in('char') IF @@ROWCOUNT=0 RETURNIF @type=2 OR NOT EXISTS(SELECT * FROM # WHERE NoChangeCause>'') BEGIN SET XACT_ABORT ON BEGIN TRAN DECLARE tb CURSOR LOCAL FOR SELECT N'ALTER TABLE '+QUOTENAME(TableName) +N' ALTER COLUMN '+QUOTENAME(FieldName) +N' VARCHAR'+QUOTENAME(FieldLen,'()') FROM # WHERE NoChangeCause IS NULL DECLARE @sql nvarchar(4000) OPEN tb FETCH tb INTO @sql WHILE @@FETCH_STATUS=0 BEGIN EXEC sp_executesql @sql FETCH tb INTO @sql END CLOSE tb DEALLOCATE tb COMMIT TRAN END--显示不能修改的列 SELECT TableName,FieldName,FieldType,FieldLen, NoChangeCause=ISNULL(NoChangeCause,N'可以修改(或者已经修改成功)') FROM # ORDER BY CASE WHEN NoChangeCause IS NULL THEN 1 ELSE 0 END,TableName--测试 create table tc(aa char(5),bb char(10),cc int,dd varchar(20))exec P_CharToVarchar 2 /* TableName FieldName FieldType FieldLen NoChangeCause ----------- --------------- ----------------- ------------------ ------------------- tc aa char(5) NULL 5 可以修改(或者已经修改成功) tc bb char(10) NULL 10 可以修改(或者已经修改成功) */select A.NAME,B.NAME,C.NAME,B.prec from sysobjects a left join syscolumns b left join systypes c on c.xusertype=b.xusertype on a.id=b.id where a.xtype='u' AND A.NAME='TC'/* NAME NAME NAME prec ------------ ---------------- ----------------- ------ tc aa varchar 5 tc bb varchar 10 tc cc int 10 tc dd varchar 20 */
as
declare tb cursor for
SELECT sql='alter table ['+d.name
+'] alter column ['+a.name+'] var'
+b.name+'('+cast(a.length*2 as varchar)+')'
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 in('char')
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)
print @sql
fetch next from tb into @sql
end
close tb
deallocate tb
go
exec P_set
'alter table '+b.name+' alter column '+a.name+' varchar(100)' from
syscolumns a,sysobjects b
where a.id=b.id and a.xtype in(175,239) and b.xtype='U'
把上面语句的到的结果贴出来,再运行
from syscolumns a
inner join sysobjects b on a.id=b.id
where a.xtype=175a.xtype=175表示数据类型为char
varchar的a.xtype=167
我现在已经导出了脚本,将char批量修改为varchar了。
然后,我该如何导入执行我修改后的脚本?
因为脚本中是create语句,会不会因为与现有表重名,而无法执行?To: wufeng4552:
我不太懂代码,您的代码中,是不是将原字段长加长了2倍?如果是这样的话,可能会有问题。
因为数据库比较重要,我会在临时数据库中试一下您的代码。再次感谢!
改成
cast(a.length
即可
/*
引用,邹建老大的代码!
*/
CREATE PROC P_CharToVarchar
@type tinyint=0 --修改方式,0=仅查询可修改情况,1=仅所有列可修改时才修改,2=修改可修改列,报告不可修改列
AS
SET NOCOUNT ON
SELECT TableName=o.name,FieldName=c.name,
FieldType=t.name+N'('+CAST(c.prec as varchar)+N')'
+CASE WHEN c.isnullable=1 THEN N'' ELSE N' NOT' END
+N' NULL',
FieldLen=CONVERT(VARCHAR,c.prec),
NoChangeCause=CAST(STUFF(
CASE WHEN COLUMNPROPERTY(c.id,c.name,N'IsComputed')=1
THEN N',计算列' ELSE N'' END
+CASE WHEN c.cdefault=0 THEN N'' ELSE N',列具有默认值' END
+CASE WHEN EXISTS(
SELECT * FROM sysindexkeys idxk,sysindexes idx
WHERE idxk.id=c.id
AND idxk.colid=c.colid
AND idxk.id=idx.id
AND idxk.indid=idx.indid
AND idx.indid NOT IN(0,255)
AND INDEXPROPERTY(idx.id,idx.name,N'IsAutoStatistics')=0)
THEN N',列被主键、唯一键、索引、STATISTICS引用' ELSE N'' END
+CASE WHEN EXISTS(
SELECT * FROM sysforeignkeys WHERE fkeyid=c.id AND fkey=c.colid)
THEN N',列被外键约束引用' ELSE N'' END
+CASE WHEN EXISTS(
SELECT * FROM sysobjects oc,sysdepends d
WHERE oc.parent_obj=o.id
AND OBJECTPROPERTY(oc.id,N'IsCheckCnst')=1
AND d.id=oc.id
AND d.depnumber=c.colid)
THEN N',列被CHECK约束引用' ELSE N'' END,1,1,N'') as nvarchar(4000))
INTO # FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id and o.status>=0
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND t.xusertype=c.xusertype
AND t.name in('char')
IF @@ROWCOUNT=0 RETURNIF @type=2 OR NOT EXISTS(SELECT * FROM # WHERE NoChangeCause>'')
BEGIN
SET XACT_ABORT ON
BEGIN TRAN
DECLARE tb CURSOR LOCAL
FOR
SELECT N'ALTER TABLE '+QUOTENAME(TableName)
+N' ALTER COLUMN '+QUOTENAME(FieldName)
+N' VARCHAR'+QUOTENAME(FieldLen,'()')
FROM #
WHERE NoChangeCause IS NULL
DECLARE @sql nvarchar(4000)
OPEN tb
FETCH tb INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
EXEC sp_executesql @sql
FETCH tb INTO @sql
END
CLOSE tb
DEALLOCATE tb
COMMIT TRAN
END--显示不能修改的列
SELECT TableName,FieldName,FieldType,FieldLen,
NoChangeCause=ISNULL(NoChangeCause,N'可以修改(或者已经修改成功)')
FROM #
ORDER BY CASE WHEN NoChangeCause IS NULL THEN 1 ELSE 0 END,TableName--测试
create table tc(aa char(5),bb char(10),cc int,dd varchar(20))exec P_CharToVarchar 2
/*
TableName FieldName FieldType FieldLen NoChangeCause
----------- --------------- ----------------- ------------------ -------------------
tc aa char(5) NULL 5 可以修改(或者已经修改成功)
tc bb char(10) NULL 10 可以修改(或者已经修改成功)
*/select A.NAME,B.NAME,C.NAME,B.prec from sysobjects a
left join syscolumns b
left join systypes c
on c.xusertype=b.xusertype
on a.id=b.id
where a.xtype='u' AND A.NAME='TC'/*
NAME NAME NAME prec
------------ ---------------- ----------------- ------
tc aa varchar 5
tc bb varchar 10
tc cc int 10
tc dd varchar 20
*/
刚才执行了您的代码,有错误提示类似于:对象'DF_<表名>_<列名>'依赖于列<列名>。是不是因为该列有默认值引起的?我该如何解决?