来个通用的(删除数据库所有用户表的数据):CREATE PROCEDURE [dbo].[sp_delete_data] AS DECLARE @tablename varchar(50)DECLARE c_tablename CURSOR FOR select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 OPEN c_tablename FETCH NEXT FROM c_tablename INTO @tablename WHILE @@FETCH_STATUS=0 BEGIN EXEC('truncate table '+@tablename) FETCH NEXT FROM c_tablename INTO @tablename END CLOSE c_tablename DEALLOCATE c_tablename
create function udf_GenLevelPath_Table() returns @v_Result table (LevelPath int,OName sysname,type varchar(8)) /****************************************************************/ /* 功能描述:按照依赖关系,列出数据库对象 */ /* 输入参数:无 */ /* 输出参数:按照依赖关系排列的数据库对象表,无依赖的在前 */ /* 编写:我 */ /* 时间:2003-9-9 */ /****************************************************************/ as begin declare @vt_ObjDepPath table (LevelPath int,OName sysname null, type Varchar(8)) declare @vt_Temp1 table (OName sysname null) declare @vt_Temp2 table (OName sysname null) declare @vi_LevelPath int set @vi_LevelPath = 1 insert into @vt_ObjDepPath(LevelPath,OName,type) select @vi_LevelPath,o.name,o.type from sysobjects o where xtype not in ('S','X')
insert into @vt_Temp1(OName) select distinct object_name(sysdepends.depid) from sysdepends,@vt_ObjDepPath p where sysdepends.id <> sysdepends.depid and p.OName = object_name(sysdepends.id)
while (select count(*) from @vt_Temp1) > 0 begin set @vi_LevelPath = @vi_LevelPath + 1
update @vt_ObjDepPath set LevelPath = @vi_LevelPath where OName in (select OName from @vt_Temp1) and LevelPath = @vi_LevelPath - 1
delete from @vt_Temp2
insert into @vt_Temp2 select * from @vt_Temp1
delete from @vt_Temp1
insert into @vt_Temp1(OName) select distinct object_name(sysdepends.depid) from sysdepends,@vt_Temp2 t2 where t2.OName = object_name(sysdepends.id) and sysdepends.id <> sysdepends.depid end select @vi_LevelPath = max(LevelPath) from @vt_ObjDepPath update @vt_ObjDepPath set LevelPath = @vi_LevelPath + 1 where OName not in (select distinct object_name(sysdepends.id) from sysdepends) and LevelPath = 1
insert into @v_Result select * from @vt_ObjDepPath order by LevelPath desc return end go--调用方法 select * from dbo.udf_GenLevelPath_Table() where type='U' go 原来的函数没有把主键、视图等分开,我把它改写了一下,这样就可以取出你定义的用户表! 然后再用游标处理就好了!
最后的 PROCEDURE 应该这样:CREATE PROCEDURE sp_ClearUserTable AS DECLARE @tablename varchar(50)DECLARE c_tablename CURSOR FOR select OName from udf_GenLevelPath_Table() where type='U' OPEN c_tablename FETCH NEXT FROM c_tablename INTO @tablename WHILE @@FETCH_STATUS=0 BEGIN EXEC('DELETE ' + @tablename ) FETCH NEXT FROM c_tablename INTO @tablename END CLOSE c_tablename DEALLOCATE c_tablename
然后在执行一下就OK
exec sp_MSForEachTable 'truncate table ...'
怎么用啊?我在联机丛书居然搜不到“sp_MSForEachTable”生成sql 脚本
能把关系和主外键,约束,默认值....都生成吗?
还有存储过程,触发器,试图,又要重新生成脚本,多麻烦啊!
我想探讨一下我的想法能否实现!
AS
DECLARE @tablename varchar(50)DECLARE c_tablename CURSOR FOR
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
OPEN c_tablename
FETCH NEXT FROM c_tablename INTO @tablename
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('truncate table '+@tablename)
FETCH NEXT FROM c_tablename INTO @tablename
END
CLOSE c_tablename
DEALLOCATE c_tablename
要按次序删
而且含外键的表不能用truncate table
你写的能做到吗?
有主外键你还得先写程序删掉所有关系
最后在重建
returns @v_Result table (LevelPath int,OName sysname,type varchar(8))
/****************************************************************/
/* 功能描述:按照依赖关系,列出数据库对象 */
/* 输入参数:无 */
/* 输出参数:按照依赖关系排列的数据库对象表,无依赖的在前 */
/* 编写:我 */
/* 时间:2003-9-9 */
/****************************************************************/
as
begin
declare @vt_ObjDepPath table (LevelPath int,OName sysname null, type Varchar(8))
declare @vt_Temp1 table (OName sysname null)
declare @vt_Temp2 table (OName sysname null)
declare @vi_LevelPath int set @vi_LevelPath = 1
insert into @vt_ObjDepPath(LevelPath,OName,type)
select @vi_LevelPath,o.name,o.type
from sysobjects o
where xtype not in ('S','X')
insert into @vt_Temp1(OName)
select distinct object_name(sysdepends.depid)
from sysdepends,@vt_ObjDepPath p
where sysdepends.id <> sysdepends.depid
and p.OName = object_name(sysdepends.id)
while (select count(*) from @vt_Temp1) > 0
begin
set @vi_LevelPath = @vi_LevelPath + 1
update @vt_ObjDepPath
set LevelPath = @vi_LevelPath
where OName in (select OName from @vt_Temp1)
and LevelPath = @vi_LevelPath - 1
delete from @vt_Temp2
insert into @vt_Temp2
select * from @vt_Temp1
delete from @vt_Temp1
insert into @vt_Temp1(OName)
select distinct object_name(sysdepends.depid)
from sysdepends,@vt_Temp2 t2
where t2.OName = object_name(sysdepends.id)
and sysdepends.id <> sysdepends.depid end select @vi_LevelPath = max(LevelPath) from @vt_ObjDepPath update @vt_ObjDepPath
set LevelPath = @vi_LevelPath + 1
where OName not in (select distinct object_name(sysdepends.id) from sysdepends)
and LevelPath = 1
insert into @v_Result
select * from @vt_ObjDepPath order by LevelPath desc
return
end
go--调用方法
select * from dbo.udf_GenLevelPath_Table() where type='U'
go
原来的函数没有把主键、视图等分开,我把它改写了一下,这样就可以取出你定义的用户表!
然后再用游标处理就好了!
AS
DECLARE @tablename varchar(50)DECLARE c_tablename CURSOR FOR
select OName from udf_GenLevelPath_Table() where type='U'
OPEN c_tablename
FETCH NEXT FROM c_tablename INTO @tablename
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DELETE ' + @tablename )
FETCH NEXT FROM c_tablename INTO @tablename
END
CLOSE c_tablename
DEALLOCATE c_tablename
击数据库-》所有任务-》生成sql脚本设置的时候,别忘了主外键都要选上,然后在执行一下就OK