删除用户表
1、select 'DROP TABLE '+name from sysobjects where type = 'U'
删除视图
2、 select 'DROP VIEW '+name from sysobjects where type = 'V'
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'
3、 select 'DROP PROC '+name from sysobjects where type = 'P'
上面的方法是复制出来,然后执行,下面的方法直接用游标执行(转)
View Code
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
删除存储过程:
<pre name="code" class="sql">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 -- 释放游标资源</pre>
< br > 以此类推吧。也可删除函数什么的
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
删除存储过程:
<pre name="code" class="sql">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 -- 释放游标资源</pre>
< br > 以此类推吧。也可删除函数什么的