HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
pointssynonymddlgeneratehowwhichobject

Problem

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.

Solution

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 ...;

Code Snippets

select dbms_metadata.get_ddl('TABLE', table_name)
from user_synonyms
where synonym_name in ...;
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 ...;

Context

StackExchange Database Administrators Q#177715, answer score: 2

Revisions (0)

No revisions yet.