declare @sql nvarchar(4000) set @sql='' select @sql=@sql+'IF object_id('''+name+''') is not null delete from '+name+'; ' from sys.objects where type='u' print @sql exec(@sql)
declare cursor1 cursor for (select name from sys.tables) declare @name varchar(200) declare @sql varchar(200) open cursor1 fetch next from cursor1 into @name while(@@fetch_status<>-1) begin set @sql=N'truncate table '+@name print @sql execute sp_executesql @sql fetch next from cursor1 into @name end
Can I use Database Publishing Wizard 1.3 to Generate Script for only Schema then run the script to recreate the database?and does the way to fit any case?
set @sql=''
select @sql=@sql+'IF object_id('''+name+''') is not null
delete from '+name+';
' from sys.objects where type='u'
print @sql
exec(@sql)
declare cursor1 cursor for (select name from sys.tables)
declare @name varchar(200)
declare @sql varchar(200)
open cursor1
fetch next from cursor1 into @name
while(@@fetch_status<>-1)
begin
set @sql=N'truncate table '+@name
print @sql
execute sp_executesql @sql
fetch next from cursor1 into @name
end
exec sp_msforeachtable 'alter table ? nocheck CONSTRAINT all' --然后删除数据
exec sp_msforEachTable 'TRUNCATE TABLE?' --再启用所有外键约束
exec sp_msforeachtable 'alter table ? check constraint all'
2008没有用过呢,挺好的
请问谁能写一个通用的Script (综合"百年树人"的想法)
SQL2005自动生成数据库的清空脚本
http://blog.csdn.net/roy_88/archive/2008/05/21/2467748.aspx
或
sp_msforeachtable 'truncate table ?'
多执行几次
from sys.objects where type='u'复制并运行得到的结果。
from sys.objects where type='u'
复制并运行得到的结果,刚才漏了个'。