为了补充Ivan的回答，我还需要删除所有用户定义的类型，所以我在脚本中添加了以下内容:

/* Drop all user-defined types */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (select TOP 1 [name] from sys.types where is_user_defined = 1)

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TYPE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Type: ' + @name
    SELECT @name = (select TOP 1 [name] from sys.types where is_user_defined = 1)
END
GO

这就是我所尝试的:

SELECT 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + ']' FROM sys.tables

无论输出什么，它将打印，只是复制和粘贴在新的查询和按执行。 这将删除所有表。

在我看来这是一个相当危险的功能。如果你要实现这样的东西，我会确保以一种你不能每次事故都运行它的方式正确地保护它。

如前所述，您可以自己创建某种存储过程。 在SQL Server 2005中，您可以查看这个系统表来确定和查找您想要删除的对象。

select * from sys.objects

尝试使用sql2012或更高版本，

这将有助于删除所选架构的所有对象

DECLARE @MySchemaName VARCHAR(50)='dbo', @sql VARCHAR(MAX)='';
DECLARE @SchemaName VARCHAR(255), @ObjectName VARCHAR(255), @ObjectType VARCHAR(255), @ObjectDesc VARCHAR(255), @Category INT;

DECLARE cur CURSOR FOR
    SELECT  (s.name)SchemaName, (o.name)ObjectName, (o.type)ObjectType,(o.type_desc)ObjectDesc,(so.category)Category
    FROM    sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN sysobjects so ON so.name=o.name
    WHERE s.name = @MySchemaName
    AND so.category=0
    AND o.type IN ('P','PC','U','V','FN','IF','TF','FS','FT','PK','TT')

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category

SET @sql='';
WHILE @@FETCH_STATUS = 0 BEGIN    
    IF @ObjectType IN('FN', 'IF', 'TF', 'FS', 'FT') SET @sql=@sql+'Drop Function '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('V') SET @sql=@sql+'Drop View '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('P') SET @sql=@sql+'Drop Procedure '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('U') SET @sql=@sql+'Drop Table '+@MySchemaName+'.'+@ObjectName+CHAR(13)

    --PRINT @ObjectName + ' | ' + @ObjectType
    FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category
END
CLOSE cur;    
DEALLOCATE cur;
SET @sql=@sql+CASE WHEN LEN(@sql)>0 THEN 'Drop Schema '+@MySchemaName+CHAR(13) ELSE '' END
PRINT @sql
--EXECUTE (@sql)

删除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');

删除所有表:

exec sp_MSforeachtable 'DROP TABLE ?'

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

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