MSSQL批量删除数据库指定表

翅膀的初衷

发表于2014-09-23 20:26:59

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

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