All Forums Database
tihoma 2 posts Joined 05/06
04 May 2006
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.

DGiabbai 47 posts Joined 07/04
04 May 2006

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 strSQL2from ( 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_DBListorder by level;

tihoma 2 posts Joined 05/06
10 May 2006

Thx for your help.

You must sign in to leave a comment.