237.将所有的char、varchar改为nchar、nvarchar

迈不过友情╰ 2023-12-27 07:43 7阅读 0赞

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