drop database ?

Database
Fan

drop database ?

Hello,

is there a store procedure or something to drop a database that has descendant. ( descendant are database or user in my problem).

I tried drop database x cascade but didn't work.

can someone help me.

Thx.

take care.

T.
2 REPLIES
Enthusiast

Re: drop database ?

You have to drop databases starting from the leafs.

Here is a query that automatically generates all the needed SQL statements for you, in the correct sequence. You'll have then to manually run the generated SQL statements.

-------------------------------------------------------------
-- NAME : DropDBChildren
-- DESCRIPTION: Generates all Drop Database/User SQL
-- statements needed to drop all children of a selected DB.
-- PARAMETERS :
-- 1) root database
-- NOTE : The "DROP" SQL Statement for the root
-- database is not generated.
-- REMARKS :
-------------------------------------------------------------
-- VERSION : 1.0, 11 Feb 2005
-- AUTHOR : Daniele Giabbai
-------------------------------------------------------------

select
/*level,*/
'GRANT ALL ON "' || DatabaseName || '" TO "' || user || '" WITH GRANT OPTION;',
'delete database "' || DatabaseName || '";' As strSQL1,
case DBKind when 'D' then
'DROP DATABASE "' || DatabaseName || '";'
else
'DROP USER "' || DatabaseName || '";'
end
As strSQL2
from
(
select
trim(A.DatabaseName) As DatabaseName
, RowType (NAMED DBKind)
,level
From
DBC.DBase A
inner join
(
-------------------------------------------------------------
-- Calc the level of childness for each database
-- and filter only databases of a defined branch
-------------------------------------------------------------
sel
DatabaseName
, rank(level DESC) as level
from
(
sel
trim(child) As DatabaseName
, count(parent) level
from dbc.children
where DatabaseName in
(select trim(child) from dbc.children
where trim(parent) = database
and trim(child) not in ('DBC')
)
group by 1
) A
) C
on A.DatabaseName = C.DatabaseName
) As CreateDatabases_DBList
order by level
;
Fan

Re: drop database ?

Thx for your help.