List All SQL Columns with Max Length and Greatest Length

List all SQL columns with max length AND greatest length

Thanks for the suggestions. I have come up with the following solution. It gets me the data i need but would be interested to see if it can be made more efficient.

declare @results table
(
ID varchar(36),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
Longest varchar(250),
SQLText varchar(250)
)

INSERT INTO @results(ID,TableName,ColumnName,DataType,MaxLength,Longest,SQLText)
SELECT 
    NEWID(),
    Object_Name(c.object_id),
    c.name,
    t.Name,
    case 
        when t.Name != 'varchar' Then 'NA'
        when c.max_length = -1 then 'Max' 
        else CAST(c.max_length as varchar)
    end,
    'NA',
    'SELECT Max(Len(' + c.name + ')) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id)
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
WHERE
    c.object_id = OBJECT_ID('MyTable')    

DECLARE @id varchar(36)
DECLARE @sql varchar(200)
declare @receiver table(theCount int)

DECLARE length_cursor CURSOR
    FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @receiver (theCount)
    exec(@sql)

    UPDATE @results
    SET Longest = (SELECT theCount FROM @receiver)
    WHERE ID = @id

    DELETE FROM @receiver

    FETCH NEXT FROM length_cursor
    INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor

SELECT 
    TableName, 
    ColumnName, 
    DataType, 
    MaxLength, 
    Longest 
FROM 
    @results

Retrieve the maximum length of a VARCHAR column in SQL Server

Use the built-in functions for length and max on the description column:

SELECT MAX(LEN(DESC)) FROM table_name;

Note that if your table is very large, there can be performance issues.

How to retrieve the MAX length for a Field defined in Table creation

If you want the official definition of the column, then you would use the metadata tables. Many databases use the standard SQL views for this purpose, which would look like:

select c.*
from information_schema.columns c
where c.table_name = 'definition' and
      c.column_name = 'measure_name';

You may also need to take the schema name into account as well.

---

Related Topics