游标删除多个表里脏数据的方法

–Title:删除数据库里某个用户所有表里的数据 –Author:wufeng4552 –Date
:2009-09-21 15:08:41 –方法1 复制代码
代码如下: declare @uname varchar(20) declare cuser cursor for select
so.name from sysobjects so,sysusers su where so.uid=su.uid and
su.name=’Stone’ and so.xtype=’U’ open cuser fetch next from cuser into
@uname while(@@fetch_status=0) begin exec(‘truncate table
[Stone].[‘+@uname+’]’) fetch next from cuser end close cuser
deallocate cuser –方法2 复制代码
代码如下: exec sp_msforeachtable @command1=”truncate table ?
;”,@whereand=’and schema_id = (select schema_id from sys.schemas where
[name] =”Stone”)’

第一种方法:

–删除表

复制代码 代码如下:

use [20130823_Recource]

CREATE proc [dbo].[delAllRecord]asdeclare @tableName
nvarchar(255)declare @Sql nvarchar(255)

go

Declare curTable Cursor for select Table_Name from
information_schema.tables where TABLE_TYPE=’BASE TABLE’Open
curTableFetch Next From curTable Into @tableName

drop table my_table1,my_table2,My_table3

WHILE(@@FETCH_STATUS = 0) BEGIN set @Sql = N’delete from ‘+@tableName
exec sp_executesql @sql Fetch Next From curTable Into @tableName
endCLOSE curTableDEALLOCATE curTable

–创建表

第二种方法:

use [20130823_Recource]

复制代码 代码如下:

go

–declare test_cursor cursor scroll for

if(exists(select * from sys.objects where name=’Student1′))

–select id,table_name from dbo.section_type

drop table Student1

–open test_cursor

go

–declare @id int

create table Student1

–declare @table_name nvarchar(50)

(

–while @@fetch_status=0

Id int primary key identity(1,2) not null,

–begin

Name nvarchar(30) not null,

–fetch next from test_cursor into @id,@table_name

Age int not null,

–print @id

MyMoney decimal ,

–print @table_name

CreateDateTime datetime default getdate()

–end

)

–close test_cursor

–插入数据

–deallocate test_cursor

insert into Student values(‘zhangsan’,34,2300,GETDATE())

–删除projectrangtree的脏数据

insert into Student

delete from projectrangtree where deleteversion0

select ‘zhangsi’,23,4300 ,GETDATE()union

delete from projectrangtree where type=3 and parentid not in(select id
from projectrangtree where type=2)

select ‘zhangwu’,33,5400,GETDATE() union

delete from projectrangtree where type=4 and parentid not in(select id
from projectrangtree where type=3)

select ‘zhanghong’,12,2300,GETDATE()

delete from projectrangtree where type=5 and parentid not in(select id
from projectrangtree where type=4)

–修改数据

–删除section_settings的脏数据

update Student set MyMoney=10000 where Age=12

delete from section_settings where parent_prj_tree_id not in(select
id from projectrangtree)

–删除数据

–删除各个表里的测点

delete Student  where Age=12

declare @table_name varchar(50)

truncate table student

declare @sql nvarchar(500)–此处要注意,声明的长度一定要够

–存储过程

–declare @measuring_point_id nvarchar(500)

if(exists(select * from sys.objects where name=’proc_Name’))

declare del_cursor cursor scroll for

drop proc proc_Name

select table_name from section_type

go

open del_cursor

create proc proc_Name(@number int,@number1 int output)

fetch next from del_cursor into @table_name

as

–print @table_name

begin

while (@@fetch_status=0)

select @number1=su.MyMoney from Student as su where su.Id=@number

begin

end

–print quotename(@table_name)

–执行存储过程

–set @measuring_point_id=’select measuring_point_id from
‘+quotename(@table_name)

declare @num int

–exec sp_executesql @measuring_point_id

exec proc_Name 3,@num output

set @sql = ‘delete from ‘+ quotename(@table_name) +’ where
measuring_point_id not in(select id from measuring_point_setting)’

print @num

exec sp_executesql @sql

–函数

–delete from @table_name where measuring_point_id not in (select id
from measuring_point_setting)

if(exists(select * from sys.objects where name=’function_Name’))

fetch next from del_cursor into @table_name

drop function function_Name

end

go

close del_cursor

create function function_Name(@number int)

deallocate del_cursor

returns int

–delete from (select talbe_name from section_type) where
measuring_point_id not in (select id from measuring_point_setting)

as

begin

declare @number1 int

select @number1=su.MyMoney from Student as su where su.Id=@number

return @number1

end

–执行函数

select dbo.function_Name(3)

–视图

if(exists(select * from sys.objects where name=’view_Name’))

drop view view_Name

go

create view view_Name

as

select * from Student where ID=3

–执行函数

select * from view_Name

–游标

declare cursor_name cursor scroll for

select su.Name from student as su