DECLARE @tableName NVARCHAR(50)= 'tablename';
DECLARE @tmpTable TABLE
(
ColumnName NVARCHAR(50) NOT NULL PRIMARY KEY ,
ColumnMaxLen INT NOT NULL
);
INSERT @tmpTable
SELECT COLUMN_NAME ,
0
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName;
DECLARE @columnName NVARCHAR(50);
DECLARE @columnMaxLen INT;
DECLARE @sql NVARCHAR(MAX);
DECLARE tmpCursor CURSOR
FOR
SELECT ColumnName
FROM @tmpTable FOR READ ONLY;
OPEN tmpCursor;
FETCH NEXT FROM tmpCursor INTO @columnName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT @columnMaxLen=ISNULL(MAX(LEN([' + @columnName
+ '])), 0) FROM ' + @tableName;
-- PRINT @sql;
EXEC sp_executesql @sql, N'@columnMaxLen int out', @columnMaxLen OUT;
UPDATE @tmpTable
SET ColumnMaxLen = @columnMaxLen
WHERE ColumnName = @columnName;
FETCH NEXT FROM tmpCursor INTO @columnName;
END;
CLOSE tmpCursor;
DEALLOCATE tmpCursor;
SELECT *
FROM @tmpTable;
