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;
查询表中所有字段内容的最大长度(大数据情况)
最新推荐文章于 2024-08-18 03:19:04 发布