Re: A Simple SQL Query for ALL_SYNONYMS

paspu_at_my-deja.com wrote:
>
> Hi All,
>
> I have the following scenario
>
> create table t1 ( col1 varchar(10) );
> create synonym S1 for T1;
> create synonym S2 for S1;
> create synonym S3 for S2;
> create synonym S4 for S3;
>
> Select synonym_name , table_name from all_synonyms where owner ='SCOTT'
> returns the following
> S1 T1
> S2 S1
> S3 S2
> S4 S3
>
> wherein i want the following output to be generated.
> S1 T1
> S2 T1
> S3 T1
> S4 T1
>
> So without creating a temporary table, how can i achieve this. Any help

This isn't so much an answer as wondering why?

Really. Why?

Wouldn't you have to have:

  create synonym S1 for T1;
  create synonym S2 for T1;
  create synonym S3 for T1;
  create synonym S4 for T1;

to get the output you require? Or write a function to recursively check the object type until it's not a synonym and use the function like this: Select synonym_name , drill_down(table_name) from all_synonyms Received on Thu Sep 21 2000 - 00:00:00 CDT