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)