CODEWITHSUNDEEP
sqlsql-servermetadata
MMakati
MMakati

Reputation: 703

Get the max length of each column dynamically SQL

I need to compare the length of the definition and the actual value in SQL.

For example:

+--------------|---------------------|-------------+
  COLUMN_NAME  | DefinitionMaxLength |ActualLength
+--------------|---------------------|-------------+
 COL 1         | 20                  | 25
 COL 2         | 30                  | 26

Here's my SQL for getting the metadata of the table.

SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH AS DefinitionMaxLength  
from INFORMATION_SCHEMA.COLUMNS as COL  
WHERE COL.DATA_TYPE ='varchar' AND COL.TABLE_NAME='TableSRC'

My problem now is how to get the max length of the actual table. I used Max(Len(COLUMN_NAME)) but this will not work.

SELECT  TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH AS DefinitionMaxLength, MAX(LEN(COLUMN_NAME))  
from INFORMATION_SCHEMA.COLUMNS as COL  
WHERE COL.DATA_TYPE ='varchar' AND COL.TABLE_NAME='TableSRC'

COLUMN_NAME is based from the output of the metadata.

Upvotes: 2

Views: 3688

Answers (2)

Andrey Morozov
Andrey Morozov

Reputation: 7979

Try This:

Declare @TableName nvarchar(100) = 'TableName'; 

SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH AS DefinitionMaxLength
, N'SELECT @resultOUT = MAX(LEN(' + COLUMN_NAME + ')) FROM ' + COL.TABLE_NAME [query]
, ORDINAL_POSITION
, 0 [ActualMaxLength]
INTO #tmp
FROM INFORMATION_SCHEMA.COLUMNS as COL 
WHERE COL.DATA_TYPE in ('varchar', 'nvarchar', 'text', 'ntext')  AND COL.TABLE_NAME = @TableName;

DECLARE
    @pos int = (SELECT MIN(ORDINAL_POSITION) FROM #tmp),
    @result int,
    @query NVARCHAR(MAX) = N'',
    @param_def NVARCHAR(100) = N'@resultOUT int OUTPUT';

WHILE EXISTS (SELECT * FROM #tmp WHERE ORDINAL_POSITION > @pos)
BEGIN
    SELECT @query = [query] FROM #tmp WHERE ORDINAL_POSITION = @pos;
    EXECUTE sp_executesql @query, @param_def, @resultOUT = @result OUTPUT;

    UPDATE #tmp SET [ActualMaxLength] = ISNULL(@result, 0) WHERE ORDINAL_POSITION = @pos;

    SET @pos = (SELECT MIN(ORDINAL_POSITION) FROM #tmp WHERE ORDINAL_POSITION > @pos);
END

SELECT COLUMN_NAME, DefinitionMaxLength, ActualMaxLength FROM #tmp;

DROP TABLE #tmp;

Upvotes: 7

T I
T I

Reputation: 9933

Do the aggregation in a cte and join to information_schema.

with data_length(column_name,  actual_max_length) as
(
select 'col1', max(len(col1))
from t
union all 
select 'col2', max(len(col2))
from t
)

select c.column_name, c.character_maximum_length,    d.actual_max_length
from information_schema.columns c
join data_length do
on d.column_name = c.column_name
where c.table_name = 't'

Upvotes: 0

Related Questions