createPROC p_typeTotype @typetinyint=0, --修改方式,0=仅查询可修改情况,1=仅所有列可修改时才修改,2=修改可修改列,报告不可修改列 @typefromnvarchar(50), @typetonvarchar(50) AS SET NOCOUNT ON --查询非unicode列转换为unicode列的可行性 SELECT TableName=o.name,FieldName=c.name, CurrentFieldType=t.name+N'('+CAST(c.prec asvarchar)+N')' +CASEWHEN c.isnullable=1THEN N''ELSE N' NOT'END +N' NULL', NoChangeCause=CAST(STUFF( CASEWHENCOLUMNPROPERTY(c.id,c.name,N'IsComputed')=1 THEN N',计算列'ELSE N''END +CASEWHEN c.cdefault=0THEN N''ELSE N',列具有默认值'END +CASEWHENEXISTS( 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 NOTIN(0,255) ANDINDEXPROPERTY(idx.id,idx.name,N'IsAutoStatistics')=0) THEN N',列被主键、唯一键、索引、STATISTICS引用'ELSE N''END +CASEWHENEXISTS( SELECT*FROM sysforeignkeys WHERE fkeyid=c.id AND fkey=c.colid) THEN N',列被外键约束引用'ELSE N''END +CASEWHENEXISTS( SELECT*FROM sysobjects oc,sysdepends d WHERE oc.parent_obj=o.id ANDOBJECTPROPERTY(oc.id,N'IsCheckCnst')=1 AND d.id=oc.id AND d.depnumber=c.colid) THEN N',列被CHECK约束引用'ELSE N''END,1,1,N'') asnvarchar(4000)) INTO # FROM sysobjects o,syscolumns c,systypes t WHERE o.id=c.id and o.status>=0 ANDOBJECTPROPERTY(o.id,N'IsUserTable')=1 AND t.xusertype=c.xusertype AND t.name in(@typefrom) IF@@ROWCOUNT=0RETURN --如果需要,修改非unicode列为unicode列 IF@type=2ORNOTEXISTS(SELECT*FROM # WHERE NoChangeCause>'') BEGIN SET XACT_ABORT ON BEGINTRAN DECLARE tb CURSOR LOCAL FOR SELECT N'ALTER TABLE '+QUOTENAME(TableName) +N' ALTER COLUMN '+QUOTENAME(FieldName) +@typeto FROM # WHERE NoChangeCause ISNULL DECLARE@sqlnvarchar(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 COMMITTRAN END --显示不能修改的列 SELECT TableName,FieldName,CurrentFieldType, NoChangeCause=ISNULL(NoChangeCause,N'可以修改(或者已经修改成功)') FROM # ORDERBYCASEWHEN NoChangeCause ISNULLTHEN1ELSE0END,TableName GO