#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    1
    Rep Power
    0

    Best way to get the max length in every column in a table


    hello,

    i'd like a query that goes through every column in a table and returns the max length of data in them. i know how to do both separately, just not sure how to join them together and show the column name and the max length within next to each other. sometimes when loading data i run into truncation errors going from the raw table to the process table, and rather than go through fiddling with the lengths in access of 100+ columns, it would be far more efficient to see exactly which column the problem lies in to correct it.

    thanks in advance,

    erik
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Posts
    608
    Rep Power
    16
    You're just looking for some way to automatically generate the query, right?

    declare @sql varchar(max)
    select @sql = @sql + ', max(length(' + column_name + ')) as Max' + column_name
    from information_schema.columns
    where table_name = 'YourTable'
    -- untested, but needed to only target text columns:
    -- and type in ('varchar', 'nvarchar' etc...)

    select @sql = 'select ' + substring(@sql, 1) /* take out first comma */ + ' from YourTable'
    sp_ExecuteSQL @sql

IMN logo majestic logo threadwatch logo seochat tools logo