0

I would like to execute the following command to retrieve the top values on all the fields of my database:

select top(100) COLUMN_NAME, count(*) as number
from DATABASE_TABLE
group by COLUMN_NAME
ORDER BY number DESC

That is, iterate through all the database tables, and all fields and calculate the top values.

How can I do this using a stored procedure or SQL query? I know how to get all the fields of the database:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS

but I don't know how to plug this into a stored procedure.

Any help would be appreciated.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Mr JaZ
  • 31
  • 2
  • You need a programming technique known as [tag:dynamic-sql] to do this. – O. Jones Oct 19 '20 at 23:36
  • you will have to query `information_schema` tables, collect column names, build dynamic SQL like this `select max(someint), max(somestring), max(somedate) from sometable`, execute it and store results in some other table for further review – T.S. Oct 19 '20 at 23:38
  • A quick google finds a whole bunch of similar questions which will give you the information you need e.g. [Q1](https://stackoverflow.com/questions/60323172/select-unique-values-from-every-column-in-every-table), [Q2](https://stackoverflow.com/questions/52246897/get-data-from-every-column-in-every-table-in-a-database-sql-server), [Q3](https://stackoverflow.com/questions/30247224/get-top-three-most-common-values-from-every-column-in-a-table).Please do your research first... – Dale K Oct 19 '20 at 23:53
  • @DaleK in this case, why haven't you vote to close? – T.S. Oct 19 '20 at 23:56

0 Answers0