- DECLARE @tableName NVARCHAR(50)= 'home';
- IF OBJECT_ID(N'TableColumnMaxLen', N'U') IS NULL
- BEGIN
- CREATE TABLE TableColumnMaxLen
- (
- TableName NVARCHAR(50) NOT NULL ,
- ColumnName NVARCHAR(50) NOT NULL ,
- ColumnMaxLen INT NOT NULL ,
- PRIMARY KEY ( TableName, ColumnName )
- );
- END;
- DECLARE @columnName NVARCHAR(50);
- DECLARE @columnMaxLen INT;
- DECLARE @sql NVARCHAR(MAX);
- DECLARE @initState INT= -1;
- DECLARE @searchingState INT= -2;
- IF NOT EXISTS ( SELECT 1
- FROM dbo.TableColumnMaxLen
- WHERE TableName = @tableName )
- BEGIN
- INSERT TableColumnMaxLen
- SELECT @tableName ,
- COLUMN_NAME ,
- @initState
- FROM INFORMATION_SCHEMA.COLUMNS
- WHERE TABLE_NAME = @tableName;
- END;
- WHILE 1 = 1
- BEGIN
- SET @columnName = ( SELECT TOP 1
- ColumnName
- FROM TableColumnMaxLen
- WHERE TableName = @tableName
- AND ColumnMaxLen = @initState
- );
- IF @columnName IS NULL
- BREAK;
- UPDATE TableColumnMaxLen
- SET ColumnMaxLen = @searchingState
- WHERE TableName = @tableName
- AND ColumnName = @columnName;
- SET @sql = 'SELECT @columnMaxLen=ISNULL(MAX(LEN([' + @columnName
- + '])), 0) FROM' + @tableName;
- -- PRINT @sql;
- EXEC sp_executesql @sql, N'@columnMaxLen int out', @columnMaxLen OUT;
- UPDATE TableColumnMaxLen
- SET ColumnMaxLen = @columnMaxLen
- WHERE TableName = @tableName
- AND ColumnName = @columnName;
- END;
- SELECT *
- FROM TableColumnMaxLen
- WHERE TableName = @tableName;
将上面 home 替换为你要查的表名称
查询效果
来源: http://www.bubuko.com/infodetail-3358083.html