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

dynamic query

What is the best way to replace the two table names with the 700 tables names in my schema?

select table_name from user_tables where table_name in ('TIOR','TIIN')
0
msimons4
Asked:
msimons4
3 Solutions
 
wdosanjosCommented:
Please try:

SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '<< your schema name >>'
0
 
slightwv (䄆 Netminder) Commented:
What do you mean 'replace'?  Are you wanting to physically rename the tables?

If you do, what about all the constraints/indexes/foreign keys on them?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
if you are interested only in your tables, then you can query USER_TABLES :

SELECT TABLE_NAME FROM USER_TABLES ;

if you are interested in all your tables + any tables on which you have got privileges, then use the below :

select table_name from ALL_tables ;

select table_name from ALL_tables where OWNER = 'SCOTT' ; -- to find only those which are owned by SCOTT user by you have privileges on them. change SCOTT to your user name accordingly.

If you are interested to find all table names in the database, then you can query DBA_TABLES

select table_name from DBA_tables where owner ='TEST_USER';  -- change test_user to actual user name

select table_name from ALL_tables ;
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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