这个好办啊。先从sysobjects中找出所有用户表,把 delete from 语句放进去 再执行。
declare @sql nvarchar(4000) set @sql=N''--清空所有用户表的内容: select @sql=@sql+N' Delete From '+NAME from (select distinct NAME from SYSOBJECTS WHERE XTYPE = 'U') T--清空指定表内容 select @sql=@sql+N' Delete From '+NAME from (select distinct NAME from SYSOBJECTS WHERE XTYPE = 'U' and Name in ('TALBE1, TABLE2,...')) TEXEC sp_executesql @SQL
方法2 用游标实现 DECLARE tables_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tables_cursor DECLARE @tablename sysname FETCH NEXT FROM tables_cursor INTO @tablename WHILE (@@FETCH_STATUS <> -1) BEGIN EXEC ('Truncate TABLE ' + @tablename) FETCH NEXT FROM tables_cursor INTO @tablename END DEALLOCATE tables_cursor
set @sql=N''--清空所有用户表的内容:
select @sql=@sql+N' Delete From '+NAME
from (select distinct NAME from SYSOBJECTS WHERE XTYPE = 'U') T--清空指定表内容
select @sql=@sql+N' Delete From '+NAME
from (select distinct NAME from SYSOBJECTS WHERE XTYPE = 'U' and Name in ('TALBE1, TABLE2,...')) TEXEC sp_executesql @SQL
Exec sp_MSforeachtable 'Truncate table ?'即可
用游标实现
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC ('Truncate TABLE ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor