第一种方法：
复制代码 代码如下:
create proc [dbo].[delallrecord]
as
declare @tablename nvarchar(255)
declare @sql nvarchar(255)
declare curtable cursor
for select table_name from information_schema.tables where table_type=’base table’
open curtable
fetch next from curtable into @tablename
while(@@fetch_status = 0)
begin
set @sql = n’delete from ‘+@tablename
exec sp_executesql @sql
fetch next from curtable into @tablename
end
close curtable
deallocate curtable
第二种方法：
复制代码 代码如下:
–declare test_cursor cursor scroll for
–select id,table_name from dbo.section_type
–open test_cursor
–declare @id int
–declare @table_name nvarchar(50)
–while @@fetch_status=0
–begin
–fetch next from test_cursor into @id,@table_name
–print @id
–print @table_name
–end
–close test_cursor
–deallocate test_cursor
–删除projectrangtree的脏数据
delete from projectrangtree where deleteversion>0
delete from projectrangtree where type=3 and parentid not in(select id from projectrangtree where type=2)
delete from projectrangtree where type=4 and parentid not in(select id from projectrangtree where type=3)
delete from projectrangtree where type=5 and parentid not in(select id from projectrangtree where type=4)
–删除section_settings的脏数据
delete from section_settings where parent_prj_tree_id not in(select id from projectrangtree)
–删除各个表里的测点
declare @table_name varchar(50)
declare @sql nvarchar(500)–此处要注意，声明的长度一定要够
–declare @measuring_point_id nvarchar(500)
declare del_cursor cursor scroll for
select table_name from section_type
open del_cursor
fetch next from del_cursor into @table_name
–print @table_name
while (@@fetch_status=0)
begin
–print quotename(@table_name)
–set @measuring_point_id=’select measuring_point_id from ‘+quotename(@table_name)
–exec sp_executesql @measuring_point_id
set @sql = ‘delete from ‘+ quotename(@table_name) +’ where measuring_point_id not in(select id from measuring_point_setting)’
exec sp_executesql @sql
–delete from @table_name where measuring_point_id not in (select id from measuring_point_setting)
fetch next from del_cursor into @table_name
end
close del_cursor
deallocate del_cursor
–delete from (select talbe_name from section_type) where measuring_point_id not in (select id from measuring_point_setting)