Find biggest number in database or table?

Question

I have a database with about 150 tables. And somewhere in those there is a value that causes an error for being too large (yes, that column should probably be corrected!) - the problem is that I don't exactly know in what table the problem is appearing. Out of the 150 tables...it's probably in a smaller collection of 10-15 tables that could be the problem.

But each of those 15 tables have multiple numeric columns and writing multiple ORDER BY's for each column is very time consuming.

Is there any way to order by biggest numeric value in a table or the entire database without specifying which columns it is by hand?

Edit

For the people who didn't read comments - the specific problem is solved. But I'd still like to know if there's a query to do it.

I think you really should be looking at the error log. How do you know that multiple tables don't have large numbers? The table with the max number may not even be the one which is failing. — Tim Biegeleisen, Sep 25 '17 at 06:47
@TimBiegeleisen Because I'm pretty certain that the value that is failing is something like and EAN-code that's scanned into a quantity field. So the number should probably be a lot larger than the others no matter what. — Danieboy, Sep 25 '17 at 06:50
@TimBiegeleisen Also I would like to know if there is a way to do this generically. Because that seems like it could be a useful query to have at hand in the future. My point is to not tunnel-vision on this specific scenario. — Danieboy, Sep 25 '17 at 06:51
But there should be a particular query failing. And that query contains particular tables and particular columns. That should be quite easy to examine. — Thorsten Kettner, Sep 25 '17 at 06:53
Dynamic sql will help you to find and query tables and columns of interest (numeric types for example ). — Serg, Sep 25 '17 at 06:56
@ThorstenKettner Yes, this specific problem could and should probably be solved this way - I agree. But the problem got me curious to know if there was a query that could find the columns in a generic way. — Danieboy, Sep 25 '17 at 06:56
If you wanted to go through all your numeric columns and, say, check whether any value exceeds one million, you'd read your system tables to get all table names and all their numeric columns and create queries programatically. If you only wanted the maximum value of all columns in all tables instead, you'd `union all` the queries. — Thorsten Kettner, Sep 25 '17 at 07:03
For the record I've found in the code where the problem was, it was an int value trying to be parsed into a Uint16 - failed because it was -1. But I'm still curious to see if there's a query to do find the biggest / smallest value. — Danieboy, Sep 25 '17 at 07:24
Maybe [this](https://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database) will help — PacoDePaco, Sep 25 '17 at 12:49
@PacoDePaco I'm a big fan of this query and have it as a stored procedure in the database already. It's somewhat what I'm looking for in style (generic) - but I couldn't manage to make it work (converting it to my current needs). — Danieboy, Sep 25 '17 at 12:50

Jeroen Mostert · Accepted Answer · 2017-09-25T13:38:19.997

Here's one quick and dirty approach that works by constructing one enormous T-SQL statement to query all columns (the result could be proficiently examined with Excel). It is extremely inefficient if your database is large; one obvious optimization is to get the maximum values of all columns per table rather than per column, which at least cuts down on the table scans. (Another is to get all the queries individually and execute them in a cursor loop with EXEC; I leave that as an exercise to the reader.) It does demonstrate a few common techniques for dynamic SQL.

SELECT REPLACE(STUFF((
    SELECT '|' + REPLACE(REPLACE(REPLACE(REPLACE(
        'SELECT $tn AS [table], $cn AS [column], CONVERT(NVARCHAR(MAX), MAX($c)) AS [max_value] FROM $t',
        '$tn', QUOTENAME(t.[name], '''')),
        '$cn', QUOTENAME(c.[name], '''')),
        '$t', QUOTENAME(t.[name])),
        '$c', QUOTENAME(c.[name]))
    FROM sys.tables t
    JOIN sys.columns c on c.[object_id] = t.[object_id]
    -- All numeric types
    WHERE c.system_type_id in (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) 
    AND t.is_ms_shipped = 0
    FOR XML PATH('')), 1, 1, ''),
    '|', 
    ' UNION ALL ' 
)

Same idea, but now query all columns at once, giving one query per table:

SELECT q FROM (
    SELECT q = 'SELECT ' + QUOTENAME(t.[name], '''') + ' AS [table]' + (
        SELECT ', MAX(' + QUOTENAME(c.[name]) + ') AS ' + QUOTENAME(c.[name])
        FROM sys.columns c 
        WHERE c.[object_id] = t.[object_id]
        -- All numeric types
        AND c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) 
        FOR XML PATH('')
    ) + ' FROM ' + QUOTENAME(t.[name]) + ';'
    FROM sys.tables t
    WHERE t.is_ms_shipped = 0
) _
WHERE q IS NOT NULL

Management Studio does not like presenting lots of queries in grid from, so when executing that many queries in one window, make sure to use "Results to Text" (Ctrl-T). This is a UI issue, not a database issue.

Sadly the database is so big that I couldn't even copy the entire result from the query, it cut off way early. But it's an interesting idea for sure! — Danieboy, Sep 25 '17 at 13:24
Simply strip off the outer layer (the one that uses `FOR XML PATH` to concatenate stuff) to get a result set with all the queries individually. You will get one query for every column in the database, wwhich you could run from a script. Don't forget to `SET NOCOUNT ON` and execute the results to text (since Management Studio is not happy with rendering 150 result sets as individual grids). — Jeroen Mostert, Sep 25 '17 at 13:27

score 0 · Answer 2 · answered Sep 25 '17 at 13:55

I don't know if it's the best solution, but with this code you can find the biggest numeric value in a table. I have found all numeric columns in the table, then the MAX value for each row and finally the biggest value.

create table myTable (
    id int identity(1,1)
    ,num1 int
    ,num2 int
    ,str1 varchar(3)
    ,str2 varchar(3)
    ,num3 int
    ,num4 bigint
    ,num5 decimal(5,2)
    ,str3 varchar(3)
);

insert into myTable values
(12,65,'A','A',6589,222,65.23,'A')
,(54,752,'B','B',212,74,1.23,'B')
,(36,75,'C','C',23,7,123.4,'C')
,(3,32,'D','D',238,786,36.74,'D')
,(7772,45,'E','E',563,42,4.23,'E')

DECLARE @columns AS nvarchar(255)
DECLARE @sql AS nvarchar(755)


;WITH CTE_numericColumns AS (
    SELECT
        COLUMN_NAME
        --,DATA_TYPE
        --,CASE WHEN column_id IS NULL THEN 0 ELSE 1 END AS [Identity]
    FROM INFORMATION_SCHEMA.COLUMNS
    LEFT JOIN sys.identity_columns
        ON OBJECT_ID(TABLE_NAME) = [object_id]
        AND COLUMN_NAME = [name]
    WHERE 1 = 1
        AND TABLE_NAME='MyTable'
        AND DATA_TYPE IN ('tinyint', 'smallint', 'int', 'bigint', 'decimal', 'numeric', 'float', 'real')
        AND [column_id] IS NULL
)
SELECT @columns = STUFF((SELECT ',(' + COLUMN_NAME + ')' FROM CTE_numericColumns FOR XML PATH('')), 1, 1, '')


SET @sql = '
    ;WITH CTE_MaxValue AS (
        SELECT(SELECT MAX([MaxValue])
            FROM (VALUES ' + @columns + ') AS [Values](MaxValue)) AS MaxValue
        FROM [MyTable]
    ) SELECT MAX(MaxValue) AS [MaxNumValue] FROM CTE_MaxValue'


exec sp_executesql @sql

Find biggest number in database or table?

Edit

2 Answers2