help me Calling Sql Server Dba People, Need A Script To Clean Up The Database

[Spartan]

oka SQL Server undi..

 

i need to delete all the databases created on it let say before May 31st 2014.

 

evarikaina idea unte..cheppandi

[Spartan]

i can use  below to remove them, kaani a names are all auto generated and chala unnai..so cannot list out..

 

 

 

 

USE master ;
GO
DROP DATABASE Sales, NewSales ;
GO

 

[MathuloMazaaahhh]

use sys.databases get the list

[Spartan]

can i use below one..

 

USE master;
Go
SELECT 'DROP DATABASE '+ name
FROM sys.databases WHERE create_date > GETDATE() - 100
GO

[Spartan]

LTT...

[Davudda]

LTT

[BC509]

use CURSOR  and dynamic sql it should work ..If need further help PM me

[Hyderabad_Nawab]

LTT

[BC509]

can i use below one..

 

USE master;
Go
SELECT 'DROP DATABASE '+ name
FROM sys.databases WHERE create_date > GETDATE() - 100
GO

 

Eventually it should work but .. Please make sure you are not dropping system db's ..

[Davudda]

help

[Spartan]

Eventually it should work but .. Please make sure you are not dropping system db's ..

 

how..

[agora]

help

[Hyderabad_Nawab]

LTT...HELP

[Spartan]

i used the below code...adi work ayindi...kaani its not dropping Db's with names starting with numbers or having "-"

 

DECLARE @sql VARCHAR (max)

DECLARE @DBname VARCHAR (50)

DECLARE DBS CURSOR FOR

SELECT name

FROM   sys.databases

WHERE  name NOT IN ( 'model', 'tempdb', 'master', 'model',

'reportserver', 'ReportServerDB', 'msdb')

OPEN DBS

FETCH next FROM DBS INTO @DBname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'DROP DATABASE ' + @DBname

PRINT @sql

--exec @sql

FETCH next FROM DBS INTO @DBname

END

CLOSE DBS

DEALLOCATE DBS

 

[BC509]

What is the error that you are getting ?

 

 

i used the below code...adi work ayindi...kaani its not dropping Db's with names starting with numbers or having "-"

 

DECLARE @sql VARCHAR (max)

DECLARE @DBname VARCHAR (50)

DECLARE DBS CURSOR FOR

SELECT name

FROM   sys.databases

WHERE  name NOT IN ( 'model', 'tempdb', 'master', 'model',

'reportserver', 'ReportServerDB', 'msdb')

OPEN DBS

FETCH next FROM DBS INTO @DBname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'DROP DATABASE ' + @DBname

PRINT @sql

--exec @sql

FETCH next FROM DBS INTO @DBname

END

CLOSE DBS

DEALLOCATE DBS