除了@Ivan的回答外，还需要包括所有类型

    /* Drop all Types */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sys.types where is_user_defined = 1 ORDER BY [name])

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 AND [name] > @name ORDER BY [name])
END
GO

还有一个样本

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

为了补充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

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

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

尝试使用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)