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

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

我正在使用Adam Anderson编写的脚本，该脚本已更新为支持dbo以外的其他模式中的对象。

declare @n char(1)
set @n = char(10)

declare @stmt nvarchar(max)

-- procedures
select @stmt = isnull( @stmt + @n, '' ) +
    'drop procedure [' + schema_name(schema_id) + '].[' + name + ']'
from sys.procedures


-- check constraints
select @stmt = isnull( @stmt + @n, '' ) +
'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + ']    drop constraint [' + name + ']'
from sys.check_constraints

-- functions
select @stmt = isnull( @stmt + @n, '' ) +
    'drop function [' + schema_name(schema_id) + '].[' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' )

-- views
select @stmt = isnull( @stmt + @n, '' ) +
    'drop view [' + schema_name(schema_id) + '].[' + name + ']'
from sys.views

-- foreign keys
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.foreign_keys

-- tables
select @stmt = isnull( @stmt + @n, '' ) +
    'drop table [' + schema_name(schema_id) + '].[' + name + ']'
from sys.tables

-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
    'drop type [' + schema_name(schema_id) + '].[' + name + ']'
from sys.types
where is_user_defined = 1


exec sp_executesql @stmt

来源:Adam Anderson的博客文章

没有一种单一的说法可以用来实现这一目标。

当然，您可以自己创建一个存储过程，用于执行这些不同的管理任务。

然后可以使用这条语句执行过程。

Exec sp_CleanDatabases @DatabaseName='DBname'

还有一个样本

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

我会在两个语句中做到:DROP DATABASE ??

然后创建数据库??

删除所有表:

exec sp_MSforeachtable 'DROP TABLE ?'

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

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