MSSQL批量删除数据库指定表

有时候因为一些原因，需要批量删除数据库的表，如果手动一个一个的删就太麻烦了，假如表之间还存在主外键，这绝对是苦差事，不过没有关系，我们可以自己写个游标，先批量清除外键，再删除所有表。

DECLARE    @name varchar(200) 
SET @name='t_dev_%' 

DECLARE sql_cursor CURSOR 
FOR 
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; ' from sysobjects where xtype = 'F' and object_name(parent_obj) like @name 

OPEN sql_cursor 
DECLARE @sqlstr sysname 
FETCH NEXT FROM sql_cursor INTO @sqlstr 
WHILE (@@FETCH_STATUS <> -1) 
BEGIN 
exec (@sqlstr) 
FETCH NEXT FROM sql_cursor INTO @sqlstr 
END 
DEALLOCATE sql_cursor 

DECLARE tables_cursor CURSOR 
FOR 
select [name] from sysobjects where xtype='u' and name like @name 
OPEN tables_cursor 
DECLARE @tablename sysname 
FETCH NEXT FROM tables_cursor INTO @tablename 
WHILE (@@FETCH_STATUS <> -1) 
BEGIN 
exec ('drop table '+@tablename) 
FETCH NEXT FROM tables_cursor INTO @tablename 
END 
DEALLOCATE tables_cursor

如果你只是想清除表中所有数据的话，把DROP TABLE这里改成TRUNCATE TABLE即可。

DECLARE    @name varchar(200) 
SET @name='t_dev_%' 

DECLARE tables_cursor CURSOR 
FOR 
select [name] from sysobjects where xtype='u' and name like @name 
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