删除一条sql语句中的所有表、存储过程、触发器、约束和所有依赖项

在SQl Server 2005中，我是否可以通过删除所有的表和存储过程、触发器、约束以及SQl语句中的所有依赖项来清理数据库?

要求理由:

我想有一个DB脚本清理现有的DB，这不是在使用，而不是创建新的，特别是当你必须把一个请求到你的DB管理员，并等待一段时间才能完成!

328811 次浏览
小开

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

当然，你可以自己创建一个stored procedure来执行这些不同的管理任务。

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

Exec sp_CleanDatabases @DatabaseName='DBname'
小开

我会用两个语句来做:DROP DATABASE ???

然后CREATE DATABASE ???

小开

删除所有表:

exec sp_MSforeachtable 'DROP TABLE ?'

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

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

小开

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

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

select * from sys.objects
小开
最佳答案

这个脚本清除所有视图，SPS，函数pk, fk和表。

/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)


SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])


WHILE @name is not null
BEGIN
SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Procedure: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO


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


SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])


WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped View: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO


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


SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])


WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Function: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO


/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)


SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)


WHILE @name is not null
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint IS NOT NULL
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
EXEC (@SQL)
PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO


/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)


SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)


WHILE @name IS NOT NULL
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint is not null
BEGIN
SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
EXEC (@SQL)
PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO


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


SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])


WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
小开

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

小开

最好的办法是“为Drop生成脚本"

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

在set Scripting选项中选择对象后，单击先进的按钮

  • < p >→设置选项“Script to create”为true(想要创建)

  • < p >→将选项“要删除的脚本”设置为true(想要删除)

  • < p >→选中复选框以选择要创建脚本的对象

  • < p >→选择编写脚本的选项(文件，新建窗口，剪贴板)

  • 默认包含依赖对象。(并将首先删除约束)

    执行脚本

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

小开

我今晚不小心在我的主数据库上运行了一个db初始化脚本。不管怎样，我很快就碰到了这个话题。我使用:exec sp_MSforeachtable 'DROP TABLE ?'回答，但必须多次执行它，直到它没有错误(依赖)。在此之后，我偶然发现了一些其他线程，并将其拼凑在一起以删除所有存储过程和函数。

DECLARE mycur CURSOR FOR select O.type_desc,schema_id,O.name
from
sys.objects             O LEFT OUTER JOIN
sys.extended_properties E ON O.object_id = E.major_id
WHERE
O.name IS NOT NULL
AND ISNULL(O.is_ms_shipped, 0) = 0
AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
AND ( O.type_desc = 'SQL_STORED_PROCEDURE' OR O.type_desc = 'SQL_SCALAR_FUNCTION' )
ORDER BY O.type_desc,O.name;


OPEN mycur;


DECLARE @schema_id int;
DECLARE @fname varchar(256);
DECLARE @sname varchar(256);
DECLARE @ftype varchar(256);


FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname;


WHILE @@FETCH_STATUS = 0
BEGIN
SET @sname = SCHEMA_NAME( @schema_id );
IF @ftype = 'SQL_STORED_PROCEDURE'
EXEC( 'DROP PROCEDURE "' + @sname + '"."' + @fname + '"' );
IF @ftype = 'SQL_SCALAR_FUNCTION'
EXEC( 'DROP FUNCTION "' + @sname + '"."' + @fname + '"' );


FETCH NEXT FROM mycur INTO @ftype, @schema_id, @fname;
END


CLOSE mycur
DEALLOCATE mycur


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

来源:AdamAnderson的博客文章

小开

在这里我找到了新的查询删除所有sp，函数和触发器

declare @procName varchar(500)
declare cur cursor


for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
小开

试试这个…

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
小开

为了补充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
无论输出什么，它将打印，只是复制所有并粘贴在新的查询和按下执行。 这将删除所有的表
小开

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

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
小开 
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
小开

试试这个

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'
小开

除了@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
小开

你必须先禁用所有triggers和constraints。

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"


EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"

在此之后，您可以生成用于删除对象的脚本

SELECT 'Drop Table '+name FROM sys.tables WHERE type='U';


SELECT 'Drop Procedure '+name FROM  sys.procedures WHERE type='P';

执行生成的语句。

小开

删除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)
小开

如果你正在开发游戏，你可以选择:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
小开

还有一个样本

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

提交答案