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