使用存储过程 修改符合条件的数据表的相关字段 的数据类型

处理思路：

查询系统表 获得符合条件的数据表 及 其列名 和 大小

通过循环 执行动态SQL语句

实现Column Type 的改变

 

本次示例效果：

将Product_开头的数据表中，varchar类型的Column类型 修改为nvarchar

 

 

代码如下：

if exists (select * from dbo.sysobjects where id = object_id(N'[spChangeProductTableVarcharColumnToNvarchar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure spChangeProductTableVarcharColumnToNvarchar


GO


/*
    exec spChangeProductTableVarcharColumnToNvarchar
*/

Create PROCEDURE dbo.spChangeProductTableVarcharColumnToNvarchar
AS
BEGIN
declare @TAB_NAME varchar(100)
declare @COL_NAME varchar(100)
declare @COL_LENGTH varchar(100)

declare @SQL_CHANGE_COLUMN_TYPE varchar(200)

declare @CURSOR_TEMP Cursor 

set @CURSOR_TEMP = cursor local  for
select T.NAME as tabName , C.NAME as colName  
 ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as colLength  
FROM syscolumns C
left join sysobjects T on C.id = T.id
where T.xtype='U'
and T.NAME Like  'Product_%'
and C.xusertype = (select xusertype from systypes where name = 'nvarchar') 
and C.name not in ('CREATED_BY','LAST_UPDATED_BY')


Open @CURSOR_TEMP
Fetch next from @CURSOR_TEMP into @TAB_NAME , @COL_NAME ,@COL_LENGTH
while @@fetch_status = 0
begin
   
   if @COL_LENGTH = '-1' 
        begin 
            set @COL_LENGTH = '(max)'
        end
   else
       begin
         set @COL_LENGTH = '(' + @COL_LENGTH +')'
       end  

   set @SQL_CHANGE_COLUMN_TYPE = 'alter table '+ @TAB_NAME +' alter column '+ @COL_NAME +' nvarchar' + @COL_LENGTH

   --print @SQL_CHANGE_COLUMN_TYPE
   EXECUTE (@SQL_CHANGE_COLUMN_TYPE)
    
    Fetch next from @CURSOR_TEMP into @TAB_NAME , @COL_NAME ,@COL_LENGTH
end
Close @CURSOR_TEMP
Deallocate @CURSOR_TEMP



END
GO