CODEWITHSUNDEEP
sqlsql-server
BobBetter
BobBetter

Reputation: 265

Select statistical operations on table without knowing column names

I am analyzing the data quality of a number of tables in regards to quantitative measures like Min, Max, Average, Standard Deviation etc.

How do I select different statistical operations (Min, Max, Standard Deviation...) on all table columns without knowing the table column names?

So something like:

Select min(col1), max(col1), stdev(col1), min(col2)... , min(colN) from table1

But making a dynamic reference to the column names because I have to run this on numerous tables with different column names and I dont want to change code every time.

I am using SQL Management Studio.

Thank you very mcuh

Upvotes: 1

Views: 195

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

Well, here is one fairly reasonable way to do such a thing:
Note You need to add the rest of the numeric data types to this example.

DECLARE @SQL nvarchar(max) = '';

SELECT @SQL = @SQL + 
'
UNION ALL
SELECT '''+ TABLE_NAME +''' As TableName, 
       '''+ COLUMN_NAME +''' As ColumnName, 
       MIN('+ COLUMN_NAME +') As [Min], 
       MAX('+ COLUMN_NAME +') As [Max], 
       STDEV('+ COLUMN_NAME +') As [STDEV]
FROM '+ TABLE_NAME
FROM information_schema.columns
WHERE DATA_TYPE IN('tinyint', 'smallint', 'int', 'bigint') -- Add other numeric data types 

SELECT @SQL = STUFF(@SQL, 1, 11, '') -- Remove the first `UNION ALL` from the query

EXEC(@SQL)

The result of this query will be structured like this:

TableName   ColumnName  Min     Max     STDEV
Table1      Col1        -123    543     100
Table1      Col2        54      72      5
Table1      Col3        0       1000    100

Upvotes: 0

Related Questions