A field in MS-SQL server batch modification table can be null

Time:2020-11-27
--Dangerous operation, remember to backup database 1 declare @ SQL varchar (500) before processing ,@tbname  varchar(100)
 2 begin
 3 
 4 -- create cursor
 5   declare cursor_item cursor fast_forward for select [name] from sysobjects where xtype='U' AND id in(select id from syscolumns where name='myColumnName' and colstat=0 )
 6   open cursor_ Item; -- Open cursor
 7 while 1 = 1 -- start loop
 8   begin
 9     fetch next from cursor_ Item into @ TBNAME; -- assign to variable
10     if(@@fetch_ Status! = 0) break; -- exit loop 11 if there is no result
12 -- splicing SQL statements for modifying fields
13     set @sql = 'alter table '[email protected]+' alter column myColumnName int NULL'
14 
15 -- perform SQL splicing
16     exec(@sql);
17 
18   end
19   close cursor_ Item -- close cursor
20   deallocate cursor_item
21 
22 end;

 

be careful:

Syscolumns a system table that holds column information
Sysobjects a system table that holds table information

Colstat = 0 is used to query the non self growing identity column

 

Recommended Today

Installation and deployment of CentOS 7

1、 Installation and configuration of virtual software virtual machine(virtual machine) refers to a complete computer system with complete hardware system functions simulated by software and running in a completely isolated environment. All the work that can be done in the real computer can be realized in the virtual machine. When creating a virtual machine in […]