Max value of each column in a table

RJ 166 Reputation points
2024-09-30T20:57:09.8933333+00:00

Hi there,

I have 500+ Azure SQL tables with total of 25000+ columns.

Most of the tables have varchar (max), or nvarchar(max) but to contain a value which could only be 3 , 5 or 20 characters long.

--for characters

select max(**len(**columnname)) from table

Similarly columns have bigint to contain value which is way less 10 or 55555

--for numbers

select max(numbercolumn) from table

I'm looking to create a query or cursor

which can scan each table and each column's max value

i tried below.

select ORDINAL_POSITION, COLUMN_NAME, data_Type into #t from INFORMATION_SCHEMA.COLUMNS where table_name = 'table1'

where data_type like '%char%'

order by 1

DECLARE @combinedString VARCHAR(max)

SELECT @combinedString = COALESCE(@combinedString + ', ', '') + t

FROM (select 'max(len(' + COLUMN_NAME +')) as '+ COLUMN_NAME as t from #t

where DATA_TYPE like '%char%' 

) a

print cast('select '+ @combinedString + ' from schema.tablename' as VARCHAR(max))

The run the result of sql.. its a manual work.

Just trying to create a output something like the below.

SchemaName, TableName, ColumnName, ExistingDataType, MaxValue

Note:
(MaxValue for int/smallint/numbers is number,

maxvalue for character is the LENGTH of characters)

Any one done something like this before?

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,822 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 111.5K Reputation points MVP
    2024-09-30T21:38:23.06+00:00

    Here is a query for the non-string type. The rest is left as an exercise for the reader:

    DROP TABLE IF EXISTS #maxvalues
    CREATE TABLE #maxvalues ("Schema"   sysname NOT NULL,
                             "Table"    sysname NOT NULL,
                             "Column"   sysname NOT NULL,
                             value      sql_variant NULL,
                             PRIMARY KEY ("Schema", "Table", "Column")
    )
    
    
    DECLARE @sql nvarchar(MAX)
    
    SELECT @sql = string_agg(concat_ws(' ', convert(nvarchar(MAX), 'SELECT'), quotename(s.name, ''''), ' AS "Schema", ',
                                     quotename(t.name, ''''), 'AS "Table",', quotename(c.name, ''''), ' AS "Column",', 
                                     'MAX(', quotename(c.name), ') FROM ', quotename(s.name), '.', quotename(t.name)),
                                nchar(13) + nchar(10)) WITHIN GROUP (ORDER BY s.name, t.name, c.column_id)
    FROM   sys.tables t
    JOIN   sys.schemas s ON t.schema_id = s.schema_id
    JOIN   sys.columns c ON t.object_id = c.object_id
    WHERE  type_name(c.system_type_id) NOT IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'image', 'next', 'bit')
      AND  max_length > -1
    
    PRINT @sql
    INSERT #maxvalues 
     EXEC (@sql)
    
    SELECT * FROM #maxvalues
    

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.