SQL Server Performance

Delete all databases

Discussion in 'SQL Server 2005 General DBA Questions' started by mherfkens, Aug 1, 2008.

  1. mherfkens New Member

    Does anyone have a good t-sql script to quickly delete several hundred databases?
  2. waqar Member

    5 min work [;)].

    CREATE TABLE AllUserDbs (ID INT IDENTITY(1,1),DBName VARCHAR(MAX))
    INSERT INTO AllUserDbs SELECT [NAME] FROM sys.databases WHERE database_id>4
    DECLARE @J INT
    DECLARE @I INT
    DECLARE @DBNAME VARCHAR(MAX)
    SET @I=1
    SELECT @J=COUNT(*) FROM AllUserDbs
    WHILE @I<=@J
    BEGIN
    SELECT @DBNAME=DBName FROM AllUserDbs WHERE ID=@I
    EXEC ('DROP DATABASE ' + @DBNAME)
    SET @I=@I+1
    END

  3. Madhivanan Moderator

    [quote user="mherfkens"]
    Does anyone have a good t-sql script to quickly delete several hundred databases?
    [/quote]
    several hundred databases or tables?
  4. Adriaan New Member

    Delete, or hide from view?
  5. prabhabk New Member

    SELECT 'DROP DATABASE'+SPACE(1)+name from sysdatabases where dbid>4 , Run this and it will produce the script to drop all the user db's. Then u need to run the output script to drop the databases.
  6. Madhivanan Moderator

    Well. Still I am waiting for replies from OP

Share This Page