转载一下邹老大的代码
Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ CREATE PROC p_ToUnicode @type tinyint=0 --修改方式,0=仅查询可修改情况,1=仅所有列可修改时才修改,2=修改可修改列,报告不可修改列 AS SET NOCOUNT ON --查询非unicode列转换为unicode列的可行性 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', 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','varchar') IF @@ROWCOUNT=0 RETURN --假如需要,修改非unicode列为unicode列 IF @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' N' FieldType 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, NoChangeCause=ISNULL(NoChangeCause,N'可以修改(或者已经修改成功)') FROM # ORDER BY CASE WHEN NoChangeCause IS NULL THEN 1 ELSE 0 END,TableName