在SQl Server 2005中,我是否可以通过删除所有的表和存储过程、触发器、约束以及SQl语句中的所有依赖项来清理数据库?
要求理由:
我想有一个DB脚本清理现有的DB,这不是在使用,而不是创建新的,特别是当你必须把一个请求到你的DB管理员,并等待一段时间才能完成!
在SQl Server 2005中,我是否可以通过删除所有的表和存储过程、触发器、约束以及SQl语句中的所有依赖项来清理数据库?
要求理由:
我想有一个DB脚本清理现有的DB,这不是在使用,而不是创建新的,特别是当你必须把一个请求到你的DB管理员,并等待一段时间才能完成!
当前回答
除了@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)