Menu
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.
How can I delete all databases easily?
452
First list all databases on your server. Use the command 'SHOW DATABASES;' in the mysql-console like in the example above. Now copy the name of the database you want to delete. To do delete a database you need the command 'DROP DATABASE'.
If you want to drop multiple databases using a single statement, you can use a comma-separated list of database names after the DROP DATABASE clause. The IF EXISTS option is available from SQL Server 2016 (13. x). It allows you to conditionally delete a database only if the database already exists.
To remove a database from the current server without deleting the files from the file system, use sp_detach_db. USE master; ALTER DATABASE [databasename] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [databasename] ; Note, database backups will not be deleted as part of the process documented above.
Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer.
You can do this through the SSMS GUI. Select the Databases node then F7 to 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.
Alternatively through TSQL you can do
EXEC sp_MSforeachdb ' IF DB_ID(''?'') > 4 BEGIN EXEC('' ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [?] '') END' 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
