Database Hierarchies

Database
Enthusiast

Database Hierarchies

Hi All,

Can someone help me to find out the query for below scenario:

We need to have a query based on DBC.DatabasesV to get lists of databases (Hierarchies) under a specific database (Master Database).

ie., Master database --> List of all child database under master database --> List of all databases under each and every child database etc...

Looking forward for your response. 

Thanks in Advance.

Srinivas.

4 REPLIES
Senior Apprentice

Re: Database Hierarchies

Hi Srinivas,

dbc.ChildrenV probably provides what you want, just try it :-)

Teradata Employee

Re: Database Hierarchies

dbc.ChildrenV will certainly be the most straightforward way, but if you are also looking for a lillte challenge, then try this :) This will give you indented list showing you DB hierarchy.


with recursive descendants(DatabaseName, DBPath, Level) as

(

    select DatabaseName

         , cast(databasename as varchar(1024)) as dbpath

         , 0

      from DBC.databasesv

     where DatabaseName = 'dbc'

    union all

    select c.DatabaseName

         , p.DBPath || '>' || c.DatabaseName

         , p.Level + 1

      from DBC.databasesv c

         , descendants p

     where p.DatabaseName = c.OwnerName

       and c.DatabaseName <> 'DBC'

)

select substring('                          ', 1, level * 2) || d.DatabaseName as DatabaseName

  from descendants d

 order by DBPath;

Enthusiast

Re: Database Hierarchies

Hi Dieter and Padhia,

Thanks for your quick responses.

--Srini.

Teradata Employee

Re: Database Hierarchies

Thanks padhia..

that's a good with recursive query.. ;)