删除用户表
1、select 'DROP TABLE '+name from sysobjects where type = 'U'
删除视图
2、
select 'DROP VIEW '+name from sysobjects where type = 'V'
删除存储过程
3、
select 'DROP PROC '+name from sysobjects where type = 'P'
上面的方法是复制出来,然后执行,下面的方法直接用游标执行(转)
删除表:
DECLARE @Tb_Name varchar(30) --定义游标操作 DECLARE staff_cursor CURSOR FOR SELECT [name] FROM sys.sysobjects WHERE type='U' -- 打开游标 OPEN staff_cursor -- 提取记录数据 FETCH Next FROM staff_cursor Into @Tb_Name WHILE @@fetch_status = 0 BEGIN EXEC('DROP TABLE ' + @Tb_Name ) PRINT @Tb_Name FETCH Next FROM staff_cursor Into @Tb_Name END CLOSE staff_cursor -- 关闭游标 DEALLOCATE staff_cursor -- 释放游标资源[/code删除存储过程:DECLARE @Sp_Name varchar(30) --定义游标操作 DECLARE @Tb_Count int SET @Tb_Count = 0 DECLARE staff_cursor CURSOR FOR SELECT [name] FROM sys.sysobjects WHERE type='p' AND Category =0 --Category =0 表示 -- 打开游标 OPEN staff_cursor -- 提取记录数据 FETCH Next FROM staff_cursor Into @Sp_Name PRINT '开始删除存储过程' WHILE @@fetch_status = 0 BEGIN SET @Tb_Count = @Tb_Count + 1 EXEC('DROP PROCEDURE ' + @Sp_Name ) PRINT CONVERT(varchar(20),@Tb_Count) + ': ' + @Sp_Name FETCH Next FROM staff_cursor Into @Sp_Name END print '总共删除' + CONVERT(varchar(20),@Tb_Count) + '个存储过程' CLOSE staff_cursor -- 关闭游标 DEALLOCATE staff_cursor -- 释放游标资源
以此类推吧。也可删除函数什么的