sql 游标使用

1.单表添加某个字段

//单表操作
declare @NAME varchar(100)
declare @sign int
set @sign=0
declare Add_ziduan cursor for(SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('表名' ))
open Add_ziduan
fetch next from Add_ziduan into @NAME
while @@fetch_status=0
begin 
if(@NAME='company_code')
set @sign=1;
fetch next from Add_ziduan into @NAME
end
if(@sign=0)
alter table '表名' add company_code varchar(50);
close Add_ziduan
deallocate Add_ziduan

2.多表添加某个字段

//给数据库中所有表中添加一个字段company_code 
declare @name varchar(100)
declare @s nvarchar(1000)
declare @sign int
set @sign=0
declare Add_ziduan cursor for(select name from sysobjects where xtype='U')
open Add_ziduan
fetch next from Add_ziduan into @name
while @@fetch_status=0
begin 
select @sign= count(*) from (SELECT NAME 'ZD' FROM SYSCOLUMNS WHERE ID=OBJECT_ID(''+@name+'' )) A where ZD='company_code'
if(@sign=0)
set @s = 'alter table ' + @name + ' add company_code varchar(50)'
Exec(@s)   
fetch next from Add_ziduan into @name
end
close Add_ziduan
deallocate Add_ziduan
GO

3.批量插入菜单权限

/**
批量插入菜单权限
**/

declare @ModuleID int
declare InsertModuleExtPermission cursor for(select ModuleID from sys_Module where M_ParentID!=0 and ModuleID not in (select distinct ModuleID from sys_ModuleExtPermission ))
open InsertModuleExtPermission
fetch next from InsertModuleExtPermission into @ModuleID
while @@FETCH_STATUS=0
begin
insert into sys_ModuleExtPermission values(@ModuleID,'查看',2),(@ModuleID,'新建',4),(@ModuleID,'修改',8),(@ModuleID,'删除',16)
fetch next from InsertModuleExtPermission into @ModuleID
end
close InsertModuleExtPermission
deallocate InsertModuleExtPermission

select *from sys_ModuleExtPermission