---删除指定数据库里的所有用户表
USE DATABASENAME
--DATABASENAME 是待清理的数据库
GO
DECLARE @au_lname VARCHAR(40),@SQLString NVARCHAR(500)
DECLARE tb CURSOR FOR
SELECT name FROM sysobjects WHERE xtype=’U’
OPEN tb 
--- Perform the FIRST FETCH.
FETCH NEXT FROM tb INTO @au_lname 
--- CHECK @@FETCH_STATUS TO see IF there ARE ANY more ROWS TO FETCH.
WHILE @@FETCH_STATUS = 0
BEGIN 
--- This IS executed AS long AS the previous FETCH succeeds.
FETCH NEXT FROM tb INTO @au_lname
SET @SQLString=’DROP TABLE ‘+ @au_lname
EXEC sp_executesql @SQLString
---DROP TABLE @au_lname
END
CLOSE tb
DEALLOCATE tb
GO

 

 

 

remark:

@@FETCH_STATUS状态

-0 FETCH 语句成功。
-1 FETCH 语句失败或此行不在结果集中。
-2 被提取的行不存在