试试这个

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'

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

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

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

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

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

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

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

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

select * from sys.objects

我在这里尝试了一些脚本，但它们不适合我，因为我的表是模式中的。所以我整理了以下内容。注意，这个脚本接受一个模式列表，然后按顺序删除。您需要确保您的模式有一个完整的顺序。如果存在任何循环依赖关系，那么它将失败。

PRINT 'Dropping whole database'
GO

------------------------------------------
-- Drop constraints
------------------------------------------
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE ['+tc2.CONSTRAINT_SCHEMA+'].[' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
Exec (@Sql)
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO


------------------------------------------
-- Drop views
------------------------------------------

DECLARE @sql VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM   sys.views v

PRINT @sql;
EXEC(@sql);
GO
------------------------------------------
-- Drop procs
------------------------------------------
PRINT 'Dropping all procs ...'
GO

DECLARE @sql VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + 'DROP PROC ' + QUOTENAME(SCHEMA_NAME(p.schema_id)) + '.' + QUOTENAME(p.name) +';' + @crlf
FROM   [sys].[procedures] p

PRINT @sql;
EXEC(@sql);
GO

------------------------------------------
-- Drop tables
------------------------------------------
PRINT 'Dropping all tables ...'
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

------------------------------------------
-- Drop sequences
------------------------------------------

PRINT 'Dropping all sequences ...'
GO
DECLARE @DropSeqSql varchar(1024)
DECLARE DropSeqCursor CURSOR FOR
SELECT DISTINCT 'DROP SEQUENCE ' + s.SEQUENCE_SCHEMA + '.' + s.SEQUENCE_NAME
    FROM INFORMATION_SCHEMA.SEQUENCES s

OPEN DropSeqCursor

FETCH NEXT FROM DropSeqCursor INTO @DropSeqSql

WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
    PRINT @DropSeqSql
    EXECUTE( @DropSeqSql )
    FETCH NEXT FROM DropSeqCursor INTO @DropSeqSql
END

CLOSE DropSeqCursor
DEALLOCATE DropSeqCursor
GO

------------------------------------------
-- Drop Schemas
------------------------------------------


DECLARE @schemas as varchar(1000) = 'StaticData,Ird,DataImport,Collateral,Report,Cds,CommonTrade,MarketData,TypeCode'
DECLARE @schemasXml as xml = cast(('<schema>'+replace(@schemas,',' ,'</schema><schema>')+'</schema>') as xml)

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT sql = 'DROP SCHEMA ['+schemaName+']' FROM 
(SELECT CAST(T.schemaName.query('text()') as VARCHAR(200)) as schemaName FROM @schemasXml.nodes('/schema') T(schemaName)) as X
JOIN information_schema.schemata S on S.schema_name = X.schemaName

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @Sql
Exec (@Sql)
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

删除所有表:

exec sp_MSforeachtable 'DROP TABLE ?'

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

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