[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • Last Modified:

sql procedure for getting max,min and count of each column in a table

I need to create a procedure or a query that gets the max,min and count for each column of a gven table.
0
PearlJamFanatic
Asked:
PearlJamFanatic
  • 4
  • 3
  • 3
  • +2
4 Solutions
 
OP_ZaharinCommented:
- you can get the list of tablename and its columnname by querying to USER_TAB_COLUMNS. then using those information, you can use scripts or procedure to get the mix, max and count as required.

- the following script will generate a set of sql and auto-populate the column name of the table that you specified. then you can copy and run that sql (see the attached image). replace tblname with the given table name:

SELECT ('select MAX(' || t.COLUMN_NAME || '), MIN(' || t.COLUMN_NAME || '), COUNT(*) from ' || t.TABLE_NAME) AS listall 
FROM user_tab_columns t WHERE t.TABLE_NAME = 'tblname' 

Open in new window



sql
0
 
PilouteCommented:
Hi,

Now as far as I can understand, you need a query or a procedure to get those infos on any database, not only oracle.

Since every database has it's own catalog, you can not have the same identification of the columns intables for any database.

For oracle, the given solution above (OP Zaharin's one) should do the trick.

For sybase, you have the info here about how to get all the columns in tables :
http://stackoverflow.com/questions/1429898/from-a-sybase-database-how-i-can-get-table-description-field-names-and-types

If there's other databases you need to 'explore', ask for them specifically...

Cheers,
P
0
 
slightwv (䄆 Netminder) Commented:
This was cross posted in Oracle and Sybase, please clarify which database.

Can you also provide a little more in about the actual requirements?  Sample data and expected results would help a lot.  You mention a procedure, so expected inputs and outputs would help.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
wilcoxonCommented:
A similar query for Sybase would be:
 
select ('select min('+c.name+'), max('+c.name+'), count(*) from '+o.name) as listall
from sysobjects o, syscolumns c
where o.id = c.id and o.name = 'tblname'
-- for all tables you could change o.name = 'tblname' to o.type = 'U'

Open in new window


count will return the same answer for every column in a table (as it just counts the rows).
0
 
wilcoxonCommented:
In Sybase, I think you could put the above in a proc and loop over the results and do an eval in order to retrieve the actual answers rather than the list of sql statements.
0
 
PearlJamFanaticAuthor Commented:
@wilcoxon the output of your query is list of queries. Now how do i get output in this form
                    count         min        max
col1name    10                 1                    5
col2name     20               6                 15
col3name      15             2                   4

This could be a procedure if not a query. I need it for sybase12, sybase15 and oracle11g
0
 
PearlJamFanaticAuthor Commented:
I am asking dumb questions because i have no experience of working with database
0
 
slightwv (䄆 Netminder) Commented:
>>@wilcoxon the output of your query is list of queries. Now how do i get output in this form

What that post was showing is using SQL to generate SQL.  You need to capture that output into a script/text file then execute the commands.
0
 
slightwv (䄆 Netminder) Commented:
>>I am asking dumb questions because i have no experience of working with database

No worries:  No matter the task at hand, we all started at the beginning...
0
 
OP_ZaharinCommented:
PearlJamFanatic, by using wilcoxon and my query, it will generate a list of queries that you can copy-then paste it to run each one of the query list. then you will get the result in max, min and count as desired.
0
 
PearlJamFanaticAuthor Commented:
Can the generated queries not run from memory? I need the code that could loop through them and generate the table as specified by me in the earlier post.
0
 
wilcoxonCommented:
For Sybase, you should be able to do so.  You will likely have to create a stored procedure that will either:

1) use a cursor to loop over the results of the above query to generate the results you want with eval
2) use nested loops to loop over tables and columns and generate the results you want
0
 
slightwv (䄆 Netminder) Commented:
>>Can the generated queries not run from memory?

In Oracle you can create some PL/SQL to do the looping similar to the suggestions above.

I assume you would want the output ins some standardized format across all the databases so you can script some sort of checking?

The output you described in http:#a35484506 isn't very standardized for automated checking.

Given your other related question, how are you planning of performing the check?  I'm sure between the Experts here, we can get the output from all three databases into a common form.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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