drop all user databases in sql server 2000

• gary1

  SSC-Insane

  Points: 21152

  More actions

  January 14, 2010 at 12:50 pm

  #215426

  Hi,

  We have SQL Server 2000 Standard Edition with SP3. We have more than 100 databases and a part of database refresh, I need to drop all the databases and then restore from production.

  Is there any single script to drop all USER databases?

  thanks

• ALI SQLDBA

  SSCrazy

  Points: 2121

  More actions

  January 14, 2010 at 2:20 pm

  #1103957

  This will help I guess

  DROP TABLE ##TEMPDBID

  GO

  CREATE TABLE ##TEMPDBID

  (

  Name sysname,

  Db_id_no int,

  sidid varchar(5000),

  mode smallint,

  status int,

  status2 bigint,

  crdate datetime,

  reserved varchar(1000),

  categry smallint,

  cmptlevel int,

  Filename varchar(5000),

  Version int,

  )

  INSERT INTO ##TEMPDBID SELECT * FROM SYS.SYSDATABASES

  DECLARE

  @query varchar(max),

  @Db_id_no int,

  @Db_name varchar(1000)

  DECLARE CUR_DBID CURSOR FOR

  SELECT Db_id_no FROM ##TEMPDBID

  OPEN CUR_DBID

  FETCH NEXT FROM CUR_DBID INTO @Db_id_no

  WHILE @@FETCH_STATUS=0

  BEGIN

  IF ((@Db_id_no > 4) ) ---- number after sysdatabases

  /* The System Databases can be eliminated from the drop Task. Depending upon the requirement,

  the @Db_id_no can be given as >4~5 excluding the Sys DBs.*/

  BEGIN

  set @Db_name = (select DB_NAME(@Db_id_no))

  SELECT @query ='drop DATABASE ' + @Db_name

  --set @query = 'Drop database @Db_name;'

  exec (@query)

  END

  FETCH NEXT FROM CUR_DBID INTO @Db_id_no

  END

  CLOSE CUR_DBID

  DEALLOCATE CUR_DBID