截断Database / SQL Server 08的所有表 [英] Truncate all tables of Database / SQL Server 08

问题描述

嗨
我试图一次性截断当前数据库的所有表。这是我写的脚本..

Hi I am trying to truncate all the tables of the current database in one shot.Here is the script i wrote ..

declare @tblname int 
declare tbl_cursor cursor for
select name from sys.tables 
open tbl_cursor
fetch next from tbl_cursor into @tblname
while @@FETCH_STATUS = 0
begin
	truncate table @tblname <-- this is where i get an error
	fetch next from tbl_cursor into @tblname
end 
close tbl_cursor
deallocate tbl_cursor 

我在截断语句中出错。请帮助我

I get an error at the truncate statement..Please help me

推荐答案

如果您使用的是SQL 2005 +然后使用以下内容：

If you are using SQL 2005+ then use the following :

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

- 禁用所有约束

EXEC sp_msforeachtableALTER TABLE？ NOCHECK CONSTRAINT所有



- 删除所有表格中的数据

EXEC sp_MSForEachTableDELETE FROM？



- 启用所有约束

EXEC sp_msforeachtableALTER TABLE？带有CHECK CHECK CONSTRAINT所有



- 某些表格有我们可能想要重新定位的标识列

EXEC sp_MSforeachtableDBCC CHECKIDENT （'？'，RESEED，0）

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

试试这个：

Try this:

declare @tblname int 
declare tbl_cursor cursor for
select name from sys.tables 
open tbl_cursor
fetch next from tbl_cursor into @tblname
while @@FETCH_STATUS = 0
begin
	EXECUTE ('truncate table '+ @tblname) <-- this is where i get an error
	fetch next from tbl_cursor into @tblname
end 
close tbl_cursor
deallocate tbl_cursor 

在依赖的情况下，这不会截断表。它会在运行时抛出错误。首先截断所有依赖表，然后尝试这个。



--Amit

In the case of dependency, this won't truncate the table. It'll throw the error at runtime. First truncate all the dependent table and then try this.

--Amit

这篇关于截断Database / SQL Server 08的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！