Max value of each column in a table

Question

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?

Accepted Answer

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

Share via

Max value of each column in a table

0 additional answers

Your answer