Sunday, October 21, 2007

Max Column Length

This script will return all the columns in a table and the maximum length of all data in them.

DECLARE ColumnList CURSOR
FOR
SELECT c.[name] AS ColumnName
FROM sys.Columns c
JOIN sys.Tables t
ON c.Object_ID = t.Object_ID
WHERE t.Name = 'reyAASerWIP'
DECLARE @ColumnName varchar(30)
OPEN ColumnList
FETCH NEXT FROM ColumnList INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ColumnName
EXECUTE('SELECT MAX(LEN(RTRIM(' + @ColumnName+ '))) AS ' + @ColumnName + ' FROM reyAASerWIP ')
FETCH NEXT FROM ColumnList INTO @ColumnName
END
CLOSE ColumnList
DEALLOCATE ColumnList

No comments:

Post a Comment