删除所有表:

exec sp_MSforeachtable 'DROP TABLE ?'

当然，这将删除除存储过程之外的所有约束、触发器等。

对于存储过程，恐怕您将需要存储在master中的另一个存储过程。

最好的方法是“为Drop生成脚本”

选择数据库->右键单击->任务->生成脚本-将打开生成脚本的向导

在set Scripting选项中选择对象后，单击高级按钮

设置选项“Script to create”为true(想要创建) 设置选项“Script to Drop”为true(想要删除) ->选中复选框，选择要创建脚本的对象 ->选择写脚本的选项(文件，新建窗口，剪贴板) 默认情况下，它包含依赖对象。(并将首先删除约束) 执行脚本

这样我们就可以自定义脚本。

删除oracle中的所有对象:

1)动态

DECLARE
CURSOR IX IS
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE ='TABLE' 
AND OWNER='SCHEMA_NAME';
 CURSOR IY IS
 SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE 
IN ('SEQUENCE',
'PROCEDURE',
'PACKAGE',
'FUNCTION',
'VIEW') AND  OWNER='SCHEMA_NAME';
 CURSOR IZ IS
 SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TYPE') AND  OWNER='SCHEMA_NAME';
BEGIN
 FOR X IN IX LOOP
   EXECUTE IMMEDIATE('DROP '||X.OBJECT_TYPE||' '||X.OBJECT_NAME|| ' CASCADE CONSTRAINT');
 END LOOP;
 FOR Y IN IY LOOP
   EXECUTE IMMEDIATE('DROP '||Y.OBJECT_TYPE||' '||Y.OBJECT_NAME);
 END LOOP;
 FOR Z IN IZ LOOP
   EXECUTE IMMEDIATE('DROP '||Z.OBJECT_TYPE||' '||Z.OBJECT_NAME||' FORCE ');
 END LOOP;
END;
/

2)静态

    SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables
        union ALL
        select 'drop '||object_type||' '|| object_name || ';' from user_objects 
        where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION')
        union ALL
        SELECT 'drop '
        ||object_type
        ||' '
        || object_name
        || ' force;'
        FROM user_objects
        WHERE object_type IN ('TYPE');

还有一个样本

declare @objectId int,  @objectName varchar(500), @schemaName varchar(500), @type nvarchar(30), @parentObjId int, @parentObjName nvarchar(500)
declare cur cursor 
for 

select obj.object_id, s.name as schema_name, obj.name, obj.type, parent_object_id
from sys.schemas s
    inner join sys.sysusers u
        on u.uid = s.principal_id
        JOIN
        sys.objects obj on obj.schema_id = s.schema_id
WHERE s.name = 'schema_name' and (type = 'p' or obj.type = 'v' or obj.type = 'u' or obj.type = 'f' or obj.type = 'fn')

order by obj.type

open cur
fetch next from cur into @objectId, @schemaName, @objectName,  @type, @parentObjId
while @@fetch_status = 0
begin
    if @type = 'p'
    begin
        exec('drop procedure ['+@schemaName +'].[' + @objectName + ']')
    end

    if @type = 'fn'
    begin
        exec('drop FUNCTION ['+@schemaName +'].[' + @objectName + ']')
    end

    if @type = 'f'
    begin
        set @parentObjName = (SELECT name from sys.objects WHERE object_id = @parentObjId)
        exec('ALTER TABLE ['+@schemaName +'].[' + @parentObjName + ']' + 'DROP CONSTRAINT ' +  @objectName)
    end

    if @type = 'u'
    begin
        exec('drop table ['+@schemaName +'].[' + @objectName + ']')
    end

    if @type = 'v'
    begin
        exec('drop view ['+@schemaName +'].[' + @objectName + ']')
    end
    fetch next from cur into  @objectId, @schemaName, @objectName,  @type, @parentObjId
end
close cur
deallocate cur

试试这个

Select 'ALTER TABLE ' + Table_Name  +'  drop constraint ' + Constraint_Name  from Information_Schema.CONSTRAINT_TABLE_USAGE

Select 'drop Procedure ' + specific_name  from Information_Schema.Routines where specific_name not like 'sp%' AND specific_name not like 'fn_%'

Select 'drop View ' + table_name  from Information_Schema.tables where Table_Type = 'VIEW'

SELECT 'DROP TRIGGER ' + name FROM sysobjects WHERE type = 'tr'

Select 'drop table ' + table_name  from Information_Schema.tables where Table_Type = 'BASE TABLE'

在这里我找到了新的查询删除所有sp，函数和触发器

declare @procName varchar(500)
declare cur cursor 

for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec('drop procedure ' + @procName)
    fetch next from cur into @procName
end
close cur
deallocate cur