How to run a single query over all the tables in a user schema in ORACLE

问题: Let say I have tables A,B,C,......,Z and I want to count all the entries in the tables and combine the results like so: SELECT 'A' AS A, count(*) from USER.A UNION . ....

问题:

Let say I have tables A,B,C,......,Z and I want to count all the entries in the tables and combine the results like so:

SELECT 'A' AS A, count(*) from USER.A UNION  
. 
. 
.
SELECT 'J' AS J, count(*) from USER.J UNION
.
.
SELECT 'Z' AS 'Z' COUNT(*) from USER.Z 

I want to avoid all above hassle. How can I do this in a smart way?.


回答1:

I tend to do this by writing a query using the user_tables Meta information View and first time selecting text that is actually an sql query:

SELECT 'select '''||TABLE_NAME||''', count(*) from '||TABLE_NAME||'union all' FROM USER_TABLES

Running that in your query tool will produce a grid of rows that are actually sql queries in their own right. Copying them out of the results grid, pasting them into the query window (remove the trailing UNION ALL) and running them again produces data for each table

select 'a', count(*) from a union all
select 'b', count(*) from b union all ....

To get more involved and include column names, there's a USER_TAB_COLUMNS view that cites info about columns (e.g. You could write a query that generates queries that search any varchar columns for a particular value)

If you get really involved it can be cleaner to use placeholders in the string and REPLACE them:

SELECT REPLACE(REPLACE(REPLACE(

  'select ''{o}.{t}'' as tname, ''{c}'' as cname 
   from {o}.{t} where 
    {c} like ''hello%'' or 
    {c} like ''goodbye%'' union all' 

  --to add more placeholders copy paste these lines 
  --and ensure there is the same number of REPLACE 
  --as there are numbers of lines 
  , '{t}', TABLE_NAME)
  , '{c}', COLUMN_NAME)
  , '{o}', OWNER)

FROM ALL_TAB_COLUMNS
WHERE DATA_TYPE = 'VARCHAR'

This is massively cleaner than starting and stopping your string all the time with || concatenation. I've deliberately uppercase the outer query and lowercase the inner query so you can tell which part is which

To add more placeholders, put additional REPLACE( at the top and copy paste in place the lines underneath that begin with a comma. Example copy , '{t}', table_name) paste and change to , '{d}', data_type) add another REPLACE( at the top and now you have a {d} placeholder you can use anywhere in the first string (the sql query pattern) to signify the data type

  • 发表于 2019-02-15 17:02
  • 阅读 ( 245 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除