批量修改SQLServer数据库表字段属性

declare c_sql cursor for
      SELECT sql = ‘alter table [‘ + d.name + ‘] alter column [‘ + a.name +
       ‘] nvarchar(‘+Convert(varchar,a.length)+‘)‘ --** 修改为什么属性
  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 = ‘varchar‘
   and not exists
 (SELECT 1
          FROM sysobjects
         where xtype = ‘PK‘
           and name in (SELECT name
                          FROM sysindexes
                         WHERE indid in (SELECT indid
                                           FROM sysindexkeys
                                          WHERE id = a.id
                                            AND colid = a.colid))) --** 排除主键修改
 order by d.name, a.name
 
 
declare @sql varchar(1000)
     open c_sql
     fetch next from c_sql into @sql
     while @@fetch_status = 0
     begin
     --select @sql
        exec(@sql)
     fetch next from c_sql into @sql
     end
     close c_sql
     deallocate c_sql 

declare c_sql cursor for
      SELECT sql = ‘alter table [‘ + d.name + ‘] alter column [‘ + a.name +
       ‘] datetime not null‘ --** 修改为什么属性
  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 = ‘smalldatetime‘
   and not exists
 (SELECT 1
          FROM sysobjects
         where xtype = ‘PK‘
           and name in (SELECT name
                          FROM sysindexes
                         WHERE indid in (SELECT indid
                                           FROM sysindexkeys
                                          WHERE id = a.id
                                            AND colid = a.colid))) --** 排除主键修改
 order by d.name, a.name
 
 
declare @sql varchar(1000)
     open c_sql
     fetch next from c_sql into @sql
     while @@fetch_status = 0
     begin
     --select @sql
        exec(@sql)
     fetch next from c_sql into @sql
     end
     close c_sql
     deallocate c_sql 

declare c_sql cursor for
      SELECT sql = ‘ALTER TABLE ‘+d.name+‘ ADD  CONSTRAINT [DF_‘+d.name+‘_‘+a.name+‘]  DEFAULT (‘‘‘‘) FOR [‘+a.name+‘]‘ --** 修改为什么属性
  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 = ‘nvarchar‘
   and not exists
 (SELECT 1
          FROM sysobjects
         where xtype = ‘PK‘
           and name in (SELECT name
                          FROM sysindexes
                         WHERE indid in (SELECT indid
                                           FROM sysindexkeys
                                          WHERE id = a.id
                                            AND colid = a.colid))) --** 排除主键修改
 order by d.name, a.name
 
 
declare @sql varchar(1000)
     open c_sql
     fetch next from c_sql into @sql
     while @@fetch_status = 0
     begin
     --select @sql
        exec(@sql)
     fetch next from c_sql into @sql
     end
     close c_sql
     deallocate c_sql