deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thx
Assuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx
|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>
|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description: Rebuilds all table indices
--Notes:
--Date: 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>