备份一个完全空的数据库。不需要删除所有对象，只需恢复备份。

还有一个样本

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

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

然后创建数据库??

试试这个…

USE DATABASE
GO
DECLARE @tname VARCHAR(150)
DECLARE @strsql VARCHAR(300)

SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' and [name] like N'TableName%' ORDER BY [name])

WHILE @tname IS NOT NULL
BEGIN
    SELECT @strsql = 'DROP TABLE [dbo].[' + RTRIM(@tname) +']'
    EXEC (@strsql)
    PRINT 'Dropped Table : ' + @tname
    SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' AND [name] like N'TableName%'  AND [name] > @tname ORDER BY [name])
END

DECLARE @name VARCHAR(255)
DECLARE @type VARCHAR(10)
DECLARE @prefix VARCHAR(255)
DECLARE @sql VARCHAR(255)

DECLARE curs CURSOR FOR
SELECT [name], xtype
FROM sysobjects
WHERE xtype IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR') -- Configuration point 1
ORDER BY name

OPEN curs
FETCH NEXT FROM curs INTO @name, @type

WHILE @@FETCH_STATUS = 0
BEGIN
-- Configuration point 2
SET @prefix = CASE @type
WHEN 'U' THEN 'DROP TABLE'
WHEN 'P' THEN 'DROP PROCEDURE'
WHEN 'FN' THEN 'DROP FUNCTION'
WHEN 'IF' THEN 'DROP FUNCTION'
WHEN 'TF' THEN 'DROP FUNCTION'
WHEN 'V' THEN 'DROP VIEW'
WHEN 'TR' THEN 'DROP TRIGGER'
END

SET @sql = @prefix + ' ' + @name
PRINT @sql
EXEC(@sql)
FETCH NEXT FROM curs INTO @name, @type
END

CLOSE curs
DEALLOCATE curs