database 如何删除 SQL Server 中的多个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5116296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to drop multiple databases in SQL Server
提问by Gargravarr
Just to clarify, ths isn't really a question, more some help for people like me who were looking for an answer.
A lot of applications create temp tables and the like, but I was surprised when Team Foundation Server created 80+ databases on my test SQL Server. TFS didn't install correctly, and kindly left me to clear up after it. Since each database had a naming convention, rather than delete each database by hand, I remembered how to use cursors and have written what I view to be the most unwise piece of T-SQL ever:
只是澄清一下，这不是一个真正的问题，对于像我这样正在寻找答案的人来说，更多的是一些帮助。
许多应用程序会创建临时表等，但当 Team Foundation Server 在我的测试 SQL Server 上创建 80 多个数据库时，我感到很惊讶。TFS 没有正确安装，请让我在它之后清理。由于每个数据库都有一个命名约定，而不是手动删除每个数据库，我记得如何使用游标并编写了我认为有史以来最不明智的 T-SQL 部分：
CREATE TABLE #databaseNames (name varchar(100) NOT NULL, db_size varchar(50), owner varchar(50), dbid int, created date, status text, compatibility_level int);
INSERT #databaseNames
exec sp_helpdb;
DECLARE dropCur CURSOR FOR
SELECT name FROM #databaseNames WHERE name like '_database_name_%';
OPEN dropCur;
DECLARE @dbName nvarchar(100);
FETCH NEXT FROM dropCur INTO @dbName;
DECLARE @statement nvarchar(200);
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = 'DROP DATABASE ' + @dbName;
EXEC sp_executesql @statement;
FETCH NEXT FROM dropCur INTO @dbName;
END
CLOSE dropCur;
DEALLOCATE dropCur;
DROP TABLE #databaseNames;
It goes without saying that using cursors like this is probably really dangerous, and should be used with extreme caution. This worked for me, and I haven't seen any further damage to my database yet, but I disclaim: use this code at your own risk, and back up your vital data first!
Also, if this should be deleted because it's not a question, I understand. Just wanted to post this somewhere people would look.
不用说，像这样使用游标可能真的很危险，应该非常小心地使用。这对我有用，我还没有看到我的数据库有任何进一步的损坏，但我否认：使用此代码风险自负，并首先备份您的重要数据！
另外，如果这不是一个问题而应该删除，我理解。只是想把这个贴在人们会看的地方。
回答by OFH
Why not just do this instead?
为什么不这样做呢？
USE master;
Go
SELECT 'DROP DATABASE ['+ name + ']'
FROM sys.databases WHERE name like '_database_name_%';
GO
Capture the output of that resultset and then paste it into another query window. Then run that. Why write all this TSQL cursor code?
捕获该结果集的输出，然后将其粘贴到另一个查询窗口中。然后运行那个。为什么要编写所有这些 TSQL 游标代码？
"When you have a hammer, everything looks like a nail!"..
“当你有锤子时，一切看起来都像钉子！”..
回答by SeriousM
this is easy...
这很简单...
use master
go
declare @dbnames nvarchar(max)
declare @statement nvarchar(max)
set @dbnames = ''
set @statement = ''
select @dbnames = @dbnames + ',[' + name + ']' from sys.databases where name like 'name.of.db%'
if len(@dbnames) = 0
begin
print 'no databases to drop'
end
else
begin
set @statement = 'drop database ' + substring(@dbnames, 2, len(@dbnames))
print @statement
exec sp_executesql @statement
end
回答by Monty Wild
There is no need to use a cursor, and no need to copy and paste SQL statements. Just run these two lines:
不需要使用游标，也不需要复制粘贴SQL语句。只需运行这两行：
DECLARE @Sql as NVARCHAR(MAX) = (SELECT 'DROP DATABASE ['+ name + ']; ' FROM sys.databases WHERE name LIKE 'DBName%' FOR XML PATH(''))
DECLARE @Sql as NVARCHAR(MAX) = (SELECT 'DROP DATABASE ['+ name + ']; ' FROM sys.databases WHERE name LIKE 'DBName%' FOR XML PATH(''))
EXEC sys.sp_executesql @Sql
EXEC sys.sp_executesql @Sql
Of course, any DB matching the criteria will be dropped immediately, so be sure that you know what you are doing.
当然，任何符合条件的数据库都会被立即删除，因此请确保您知道自己在做什么。
