How to generate DDL of an object which a synonym points to?

Question

I want to generate the DDL of an object which a synonym points to. Consider the below example.

I have a synonym named USER which points to USER_1 table. I want to generate DDL of the USER_1 table using the synonym as input.

I need to generate for over 400 synonyms, hence searching for a method to retrieve DDL using synonyms.

Thanks in advance.

Answer 1

For tables:

select dbms_metadata.get_ddl('TABLE', table_name)
from user_synonyms
where synonym_name in ...;

For other object types as well:

select dbms_metadata.get_ddl(o.object_type, s.table_name)
from user_synonyms s join user_objects o on (s.table_name = o.object_name)
where s.synonym_name in ...;