删除用户表

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'  

上面的方法是复制出来，然后执行，下面的方法直接用游标执行（转）

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>以此类推吧。也可删除函数什么的