How to query max(len) from all columns?

Question

I have temp table which contains nearly 200 columns. I need to know the max length of each column such that I can rectify the warning ''string or binary data would be truncated''. Now I can't mention all columns like

select max(len(col1)),max(len(col2))....,,, from #tableA

Now how can I get max length of all columns?

Wouldn't it be easier to read the INFORMATION_SCHEMA table to get the column sizes? — pritaeas, Jun 08 '15 at 06:55
Check this question: http://stackoverflow.com/questions/14482897/list-all-sql-columns-with-max-length-and-greatest-length — Reena Parekh, Jun 08 '15 at 06:58
I don't want to get it from schema table. I want to find the length of table columns by constructing the query — Giri Prasad, Jun 08 '15 at 07:09
For all the people telling everyone to check the schema, the schema tells you how many spaces are allotted, not how many are actually there. If someone is crafting a table you probably want to know what the best number should be for the field types, which is what the question really is. — Altimus Prime, Nov 09 '17 at 15:09

score 2 · Answer 1 · answered Apr 03 '18 at 03:56

I had this exact same issue, and found the code below (complete copy/paste from the site) which did the trick for me. Unfortunately, I had to go through a few iterations of data loading to work out every column that was the culprit, but was able to resolve everything in the end:

/**************************************
SQL to get max length of values in every table column.

This SQL queries the specified table to get the max length 
of all the values in every column.  

To work, load all the incoming data into a "permanent" temporary table 
where every column is defined as a varchar(max).

Then run this script against that "permanent" temporary table, comparing 
the results against the original table's schema to see what column, and
then what record(s), is/are causing the issue.

Example, if the max length of a value in one of the columns is 270, 
but your original table's schema is varchar(255), obviously either 
the original table's schema will have to be altered or the data corrected. 

Code from: http://cc.davelozinski.com
**************************************/

--The table we'll be performing this query on to get the lengths of every column.
--Default is dbo schema. Change as appropriate for your table.
DECLARE @TableName VARCHAR(200) = 'Your table name to query'
       ,@SchemaName VARCHAR(200) = 'dbo'

DECLARE @MaxLengthDefault INT
       ,@Column VARCHAR(50)
       ,@MaxLength INT
       ,@MaxLengthString VARCHAR(10)
       ,@ColumnID INT
       ,@MaxColumnID INT
       ,@Command VARCHAR(2000)

CREATE TABLE #Temp (
       column_name VARCHAR(50)
       ,max_length INT
       ,max_length_default INT
)

SELECT @ColumnID = min(b.[column_id])
       ,@MaxColumnID = max(b.[column_id]) 
FROM sys.tables a
INNER JOIN sys.columns b on a.[object_id] = b.[object_id] 
WHERE a.[name] = @TableName 
       and SCHEMA_NAME(a.[schema_id]) = @SchemaName    

--SELECT @ColumnID, @MaxColumnID

WHILE(@ColumnID <= @MaxColumnID)
BEGIN
    SET @Column = null

    SELECT @Column = b.[name]
              ,@MaxLengthDefault = b.[max_length]
       FROM sys.tables a
       INNER JOIN sys.columns b on a.[object_id] = b.[object_id]
       WHERE a.[name] = @TableName
              and SCHEMA_NAME(a.[schema_id]) = @SchemaName
              and b.[column_id] = @ColumnID

    --SELECT @Column, @MaxLengthDefault

    IF ( @Column is not null )
    BEGIN
              SET @Command = 'INSERT INTO #Temp(column_name, max_length, max_length_default) 
                SELECT ''' + @Column + '''
                ,MAX(LEN(CAST([' + @Column + '] as VARCHAR(8000))))
                ,' + CAST(@MaxLengthDefault as VARCHAR(5)) + 
                ' FROM [' + @SchemaName + '].[' + @TableName + '] 
                WHERE [' + @Column + '] IS NOT NULL'
              --SELECT @Command
              EXEC(@Command)        
       END

    SET @ColumnID = @ColumnID + 1
END

SELECT * FROM #Temp

DROP TABLE #Temp

all my NVARCHAR max_ength_defaults are doubled w/ this – Poat Mar 06 '23 at 15:42 — Poat, Mar 06 '23 at 15:42

score 1 · Answer 2 · answered Jun 08 '15 at 07:27

Yes you can use INFORMATION_SCHEMA.COLUMNS for any DB.

Prefix your db name. In your case use tempdb.INFORMATION_SCHEMA.COLUMNS

SELECT COLUMN_NAME,
       CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION
FROM   tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE table_name like  '%tableA%'

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

Try this :

SELECT COLUMN_NAME,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TableName'

Please find more information here How can I get column names from a table in SQL Server?

edited May 23 '17 at 11:58

Community

1
1

answered Jun 08 '15 at 07:04

Neo Vijay

823
9
13

I'm doubting that can it give columns of temp table..??? And How can I construct my query as I shown in my question? – Giri Prasad Jun 08 '15 at 07:07

score 0 · Answer 4 · answered Jun 08 '15 at 07:26

0

To get max length of all columns of temp table you can write a query as:

select name,max_length
from tempdb.sys.columns 
where object_id = object_id('tempdb..#Temp');

answered Jun 08 '15 at 07:26

Deepshikha

9,896
2
21
21

How to query max(len) from all columns?

4 Answers4