none
Find Table Column Names and Max Column Length?

    Question

  • Hi,

    Am looking for a script that would list all column names in a given table and corresponding

    Maximum column length i.e. MAX(LEN(LTRIM(ColumnName)))

    Example:

    Table_Name    Column_Name     Max_Length   DataType     Width

    CarMaster             Vehicle_Id              10                     int             NULL

    CarMaster              FirstName              20                   varchar       50

    CarMaster              Surname                30                     varchar       50

    CarMaster             Address                  41                      varchar       60

    Cheers

    Tuesday, December 06, 2011 4:16 AM

Answers

  • Try:

    declare @TableName sysname = 'Items'
    declare @SQL nvarchar(max)
    
    select @SQL = stuff((select 
    '
    UNION ALL 
    select ' + quotename(Table_Name,'''') + ' AS Table_Name, ' + 
    quotename(Column_Name,'''') + ' AS ColumnName, MAX(LEN(LTRIM(' + QUOTENAME(Column_Name) + 
    '))) as [Max Length], ' + quotename(C.DATA_TYPE,'''') + ' AS Data_Type, ' + 
    cast(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE) as varchar(10)) +'  AS Data_Width FROM ' + QUOTENAME(Table_Name)
    from INFORMATION_SCHEMA.COLUMNS C 
    WHERE TABLE_NAME = @TableName and DATA_TYPE NOT LIKE '%text'
    FOR XML PATH(''),type).value('.','varchar(max)'),1,11,'')  
    --print @SQL
    execute (@SQL)
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by sql baby Tuesday, December 06, 2011 4:56 AM
    Tuesday, December 06, 2011 4:46 AM
  • Right, I made a short blog post 

    Maximum length of data in every column in a table


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by sql baby Tuesday, December 06, 2011 11:05 PM
    Tuesday, December 06, 2011 3:58 PM

All replies

  • Try:

    declare @TableName sysname = 'Items'
    declare @SQL nvarchar(max)
    
    select @SQL = stuff((select 
    '
    UNION ALL 
    select ' + quotename(Table_Name,'''') + ' AS Table_Name, ' + 
    quotename(Column_Name,'''') + ' AS ColumnName, MAX(LEN(LTRIM(' + QUOTENAME(Column_Name) + 
    '))) as [Max Length], ' + quotename(C.DATA_TYPE,'''') + ' AS Data_Type, ' + 
    cast(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE) as varchar(10)) +'  AS Data_Width FROM ' + QUOTENAME(Table_Name)
    from INFORMATION_SCHEMA.COLUMNS C 
    WHERE TABLE_NAME = @TableName and DATA_TYPE NOT LIKE '%text'
    FOR XML PATH(''),type).value('.','varchar(max)'),1,11,'')  
    --print @SQL
    execute (@SQL)
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by sql baby Tuesday, December 06, 2011 4:56 AM
    Tuesday, December 06, 2011 4:46 AM
  • I think you may also need to check for TABLE_SCHEMA otherwise only tables in the dbo schema will return any data using this code.
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Tuesday, December 06, 2011 5:07 AM
  • Right, I made a short blog post 

    Maximum length of data in every column in a table


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by sql baby Tuesday, December 06, 2011 11:05 PM
    Tuesday, December 06, 2011 3:58 PM
  • Thanks Naomi - exactly what i needed!

     

    Tuesday, December 06, 2011 10:57 PM
  • Cheers Jeff : have schema other than dbo in our current DB, so a very helpful tip.
    Tuesday, December 06, 2011 10:58 PM