SQL 从服务器删除所有数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5777483/
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
Drop all databases from server
提问by Moslem7026
I have a server (SQL Server 2005) with more than 300 databases. I don't want to right-click one by one and select Delete.
我有一台服务器 (SQL Server 2005) 有 300 多个数据库。不想一一右击选择Delete。
How can I delete all databases easily?
如何轻松删除所有数据库？
回答by Martin Smith
You can do this through the SSMS GUI. Select the Databasesnode then F7to bring up Object Explorer Details, Select all databases that you want to delete, Hit "Delete" and select the "Close Existing Connections" and "Continue after error" options.
您可以通过 SSMS GUI 执行此操作。选择Databases节点，然后F7打开对象资源管理器详细信息，选择要删除的所有数据库，点击“删除”并选择“关闭现有连接”和“出错后继续”选项。
Alternatively through TSQL you can do
或者通过 TSQL 你可以做
EXEC sp_MSforeachdb '
IF DB_ID(''?'') > 4
BEGIN
EXEC(''
ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [?]
'')
END'
回答by Pellared
And here is my solution for the same problem:
这是我对同一问题的解决方案：
-- drops all user databases
DECLARE @command nvarchar(max)
SET @command = ''
SELECT @command = @command
+ 'ALTER DATABASE [' + [name] + '] SET single_user with rollback immediate;'+CHAR(13)+CHAR(10)
+ 'DROP DATABASE [' + [name] +'];'+CHAR(13)+CHAR(10)
FROM [master].[sys].[databases]
where [name] not in ( 'master', 'model', 'msdb', 'tempdb');
SELECT @command
EXECUTE sp_executesql @command
回答by Christopher Bantick
This will kill all connections, and delete all databases not in the list:
这将终止所有连接，并删除不在列表中的所有数据库：
('master' ,'tempdb' ,'model' ,'msdb' ,'ReportServer' ,'ReportServerTempDB')
('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
use [master]
DECLARE
@DATABASENAME nVARCHAR(20)
DECLARE
@TABLE TABLE
(NAME nVARCHAR(50))
Declare @SQL nvarchar(100)
INSERT INTO @TABLE
SELECT
name
FROM sys.databases
WHERE name not in
('master'
,'tempdb'
,'model'
,'msdb'
,'ReportServer'
,'ReportServerTempDB')
while (select COUNT(*) from @table) > 0
begin
select @DATABASENAME = (select top 1 (name) from @TABLE)
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id(@DATABASENAME)
EXEC(@kill);
set @SQL = 'drop database ' + @DATABASENAME
exec sp_executesql @SQL, N'@databasename nvarchar(50)', @databasename;
print @databasename + ' has been deleted'
delete from @TABLE where NAME = @DATABASENAME
end
回答by Ashraf Abusada
The safe way of deleting (dropping) all databases in MS-SQL using T-SQL is to exclude all system databases, and any other database(s) that you want to keep, and special databases such as Data Warehouse database "DW", Report server database.
使用 T-SQL 在 MS-SQL 中删除（删除）所有数据库的安全方法是排除所有系统数据库，以及您想要保留的任何其他数据库，以及特殊数据库，例如数据仓库数据库“DW”，报表服务器数据库。
Excluding all the databases that we want to keep including all system databases will make it safe to delete just everything else that we don't want to keep.
排除我们想要保留的所有数据库，包括所有系统数据库，可以安全地删除我们不想保留的所有其他内容。
For example:
例如：
use master
go
declare @dbnames nvarchar(max)
declare @statement nvarchar(max)
set @dbnames = ''
set @statement = ''
select @dbnames = @dbnames + ',[' + name + ']' from sys.databases
where name
NOT IN ('master','model','msdb','tempdb')
AND name NOT LIKE '%AdventureWorks%' -- Database to keep
AND name NOT LIKE '%DW%' -- Data warehouse database
AND name NOT LIKE '%ReportServer%' -- Report server database
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
go
回答by Gangai Johann
I've been confronted to a problem with my databases, and the solutions above weren't working.
我的数据库遇到了问题，上面的解决方案不起作用。
I simply wanted to delete all my databases, but I had a problem with their names. Indeed, I had databases named like this :
我只是想删除我所有的数据库，但他们的名字有问题。事实上，我有这样命名的数据库：
093e83d-somename;
39s2ak3-anothername;
The easier way to delete these database (tested on MariaDB) is to execute the following command :
删除这些数据库的更简单方法（在MariaDB 上测试）是执行以下命令：
DROP DATABASE `093e83d-somename`;
This kind of name seems to be a problem when we directly want to execute a SQL command from a bash file, because we have to specify the database's name between back quotes (``).
当我们直接想从 bash 文件中执行 SQL 命令时，这种名称似乎是一个问题，因为我们必须在反引号 (``) 之间指定数据库的名称。
If you have the same problem, and if you have a lot of databases, you just have to create a batch script with all the commands you need, and then execute this one in your SQL server.
如果你有同样的问题，如果你有很多数据库，你只需要用你需要的所有命令创建一个批处理脚本，然后在你的 SQL 服务器中执行这个。
Example with test.sh:
test.sh示例：
#!/bin/bash
# Informations needed
MUSER="root"
MPASS="pass"
# We get the needed binaries
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)
# We get all the DB names in DB
DB=$($MYSQL -u $MUSER -p$MPASS -e 'show databases' | $AWK '{ print }' | $GREP -v '^Databases' )
# For each database, we write the drop command in the file test.sql
for t in $DB
do
echo -e "DROP DATABASE \`$t\`;" >> test.sql
done
# We execute the created SQL file with all the DROP commands
$MYSQL -u $MUSER -p$MPASS -e 'source test.sql;'
# We finally delete the created file.
rm test.sql
I think that this script is working in all cases. Hope this helped.
我认为这个脚本在所有情况下都有效。希望这有帮助。
回答by Bobby Cannon
Here is my PowerShell script version. DropAllDatabases.ps1
这是我的 PowerShell 脚本版本。DropAllDatabases.ps1
$sqlCmdPath="C:\Program Files\Microsoft SQL Server\Client SDK\ODBC0\Tools\Binn\sqlcmd.exe"
& $sqlCmdPath -S localhost -Q "EXEC sp_MSforeachdb 'IF DB_ID(''?'') > 4 BEGIN PRINT ''?'' ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [?] END'"
Note:You may need to update the $sqlCmdPath to your version of SQL.
注意：您可能需要将 $sqlCmdPath 更新为您的 SQL 版本。
回答by sonyisda1
While MartinSmith's answer is the correct solution. I found the Delete Objects window just sitting with status of 'in process' while deleting the first of many databases. This was due to an overgrown msdb backup history which was attempting to be cleaned up with the 'Delete backup and restore history information for databases' option checked.
而 MartinSmith 的答案是正确的解决方案。我发现删除对象窗口只是在删除许多数据库中的第一个时处于“处理中”状态。这是由于过度增长的 msdb 备份历史记录试图在选中“删除数据库的备份和还原历史记录信息”选项的情况下进行清理。
Refer to these links for more info on cleaning up backup history 12
After adding the indexes provided here, the deletion processed through in reasonable time.
添加这里提供的索引后，删除处理在合理的时间内完成。
回答by Peter Hecht
I used the answer provided by Pellared and modified it slightly.
我使用了 Pellared 提供的答案并稍微修改了它。
-- drops all user databases
DECLARE @command nvarchar(max)
SET @command = ''
SELECT @command = @command
+ 'ALTER DATABASE [' + [name] + '] SET single_user with rollback immediate;'+CHAR(13)+CHAR(10)
+ 'DROP DATABASE [' + [name] +'];'+CHAR(13)+CHAR(10)
FROM [master].[sys].[databases]
where [name] like 'DBName%';
-- PRINT @COMMAND
EXECUTE sp_executesql @command
回答by Filip Holub
You can use Cursor like this:
您可以像这样使用 Cursor：
DECLARE @DBName VARCHAR (64)
DECLARE @SQL VARCHAR (255)
DECLARE DROPDB CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','ReportServer','ReportServerTempDB')
OPEN DROPDB
FETCH next FROM DROPDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'DROP DATABASE ' + @DBName
PRINT @SQL
EXEC @SQL
FETCH next FROM DROPDB INTO @DBName
END
CLOSE DROPDB
DEALLOCATE DROPDB
In my blog is more about this topic. www.pigeonsql.com
在我的博客中有更多关于这个话题的内容。 www.pigeonsql.com
