在SQl Server 2005中，我是否可以通过删除所有的表和存储过程、触发器、约束以及SQl语句中的所有依赖项来清理数据库?
要求理由:
我想有一个DB脚本清理现有的DB，这不是在使用，而不是创建新的，特别是当你必须把一个请求到你的DB管理员，并等待一段时间才能完成!
当前回答
还有一个样本
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
其他回答
没有一种单一的说法可以用来实现这一目标。
当然，您可以自己创建一个存储过程，用于执行这些不同的管理任务。
然后可以使用这条语句执行过程。
Exec sp_CleanDatabases @DatabaseName='DBname'
我在这里尝试了一些脚本，但它们不适合我，因为我的表是模式中的。所以我整理了以下内容。注意，这个脚本接受一个模式列表，然后按顺序删除。您需要确保您的模式有一个完整的顺序。如果存在任何循环依赖关系，那么它将失败。
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 @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
试试这个…
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
您必须首先禁用所有触发器和约束。
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';
执行生成的语句。
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
