• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1041
  • Last Modified:

select to determine which tables don't have a public synonym

I am trying to write a select looking at DBA_TABLES and DBA_SYNONYMS that will list for me the tables that do not  have a public synonym. I have tried using an outerjoin statement, but I get all tables...not just the ones with no public synonym. Can you assist?
0
ShirleyTanner
Asked:
ShirleyTanner
  • 4
  • 2
2 Solutions
 
sdstuberCommented:
select * from dba_tables
where (owner,table_name) not in
(select table_owner,table_name from dba_synonyms where owner = 'PUBLIC')
0
 
awking00Commented:
See attached.
synonyms.txt
0
 
sdstuberCommented:
or, if you want the outer join...


select * from dba_tables t left outer join dba_synonyms s
on  t.owner = s.table_owner
and t.table_name = s.table_name
and s.owner ='PUBLIC'
where t.owner ='SYSTEM'
and s.table_owner is null
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
sdstuberCommented:
remove the "SYSTEM" check, that was just for my own testing for a small schema
sorry for any confusion

select * from dba_tables t left outer join dba_synonyms s
on  t.owner = s.table_owner
and t.table_name = s.table_name
and s.owner ='PUBLIC'
where s.table_owner is null
0
 
ShirleyTannerAuthor Commented:
gr8...how do I exclude system objects?

Also, what about the other way... select tables that have a private synonym?
0
 
sdstuberCommented:
-- To exclude some schemas...
select * from dba_tables t left outer join dba_synonyms s
on  t.owner = s.table_owner
and t.table_name = s.table_name
and s.owner ='PUBLIC'
where s.table_owner is null
and t.owner not in ('SYSTEM','SYS','CTXSYS')
                 -- add any other schemas you want to EXclude to the "IN" list.


to find tables that have private synonyms

select * from dba_tables t inner join dba_synonyms s
on  t.owner = s.table_owner
and t.table_name = s.table_name
and s.owner != 'PUBLIC'
where t.owner not in ('SYSTEM','SYS','CTXSYS')   -- again change this build an EXclude list
0
 
awking00Commented:
Query modified to correct "synonym_name" to "table_name"
synonyms.txt
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now