• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Deleting multiple sql databases

Hi Experts, I need to delete multiple databases (200+) which start with 2014 Oct - Dec_* where * is a random number and that number changes. Is there a query I can run to do that? SQL is 2008

TIA
0
abhijitm00
Asked:
abhijitm00
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
using this basic code to process several record:
http://www.experts-exchange.com/Database/MS-SQL-Server/A_13640-processing-cursor-vs-temp-table-syntax.html

-- starting with the variables you will need in the processing part
declare @id int
declare @name sysname
declare @l nvarchar(max)

-- here is the table variable definition, which lives only for the duration of the run and is cleaned up automatically
-- for "small" results, it will stay purely in memory; larger sets may be "stored" in the temp database
declare @c table ( id int , name sysname )
-- fill the table with data, update your select as you need it
-- if your process requires some "order" in the processing, still an ORDER BY here will be useless (unless you also use and need a TOP X clause) see below on where you have to put the ORDER BY

set nocount on
insert into @c (id, name) select id, name from sysdatabases where name like '2014 Oct - Dec_%' ;

-- process, either if the above insert did at least 1 row, or if the below "delete" did indeed 
while @@ROWCOUNT <> 0
begin
  -- fetch 1 record from the table. 
  -- if your process requires some "order" in the processing, put the corresponding ORDER BY here
  select top 1 @id = id, @name = name from @c

  if @@ROWCOUNT <> 0
  begin
   -- process the data here, exactly the same as with the cursor example.
   set @l = 'DROP DATABASE [' + @name + ']'
   exec(@l)
  end

  -- here we delete the row from the temp table , which is nothing else than a processing queue table, hence
  delete @c where id = @id
END 

Open in new window

0
 
abhijitm00Author Commented:
Thanks Guy. I am getting this error:

Msg 207, Level 16, State 1, Line 13
Invalid column name 'id'.
0
 
Scott PletcherSenior DBACommented:
IF OBJECT_ID('tempdb.dbo.#drop_database_commands') IS NOT NULL
    DROP TABLE #drop_database_commands
CREATE TABLE #drop_database_commands (
    command nvarchar(200) NOT NULL
    );

INSERT INTO #drop_database_commands
SELECT 'DROP DATABASE [' + name + ']; '
FROM sys.databases
WHERE name LIKE '2014 Oct - Dec_[0-9]%'

SELECT * FROM #drop_database_commands

--review the commands that are output, and if they are correct, run them.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
abhijitm00Author Commented:
Hi Scott, the results are correct. How do I run the command to drop them now? Thanks
0
 
abhijitm00Author Commented:
Never mind, got it. Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry I got a small error in the code...
-- starting with the variables you will need in the processing part
declare @name sysname
declare @l nvarchar(max)

-- here is the table variable definition, which lives only for the duration of the run and is cleaned up automatically
-- for "small" results, it will stay purely in memory; larger sets may be "stored" in the temp database
declare @c table (  name sysname )
-- fill the table with data, update your select as you need it
-- if your process requires some "order" in the processing, still an ORDER BY here will be useless (unless you also use and need a TOP X clause) see below on where you have to put the ORDER BY

set nocount on
insert into @c ( name) select  name from sysdatabases where name like '2014 Oct - Dec_%' ;

-- process, either if the above insert did at least 1 row, or if the below "delete" did indeed 
while @@ROWCOUNT <> 0
begin
  -- fetch 1 record from the table. 
  -- if your process requires some "order" in the processing, put the corresponding ORDER BY here
  select top 1 @name = name from @c

  if @@ROWCOUNT <> 0
  begin
   -- process the data here, exactly the same as with the cursor example.
   set @l = 'DROP DATABASE [' + @name + ']'
   exec(@l)
  end

  -- here we delete the row from the temp table , which is nothing else than a processing queue table, hence
  delete @c where name = @name 
END 
                                          

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now