MSSQL获取表字段最大长度

本文主要是介绍MSSQL获取表字段最大长度，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

/*获取表字段最大长度
将CRM_ORDER_STANDARD换成需要查询的表即可
*/
DECLARE @tableName NVARCHAR(50)= 'crm_order_standard';IF OBJECT_ID(N'TableColumnMaxLen', N'U') IS  NULLBEGINCREATE 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  1FROM    dbo.TableColumnMaxLenWHERE   TableName = @tableName )BEGININSERT  TableColumnMaxLenSELECT  @tableName ,COLUMN_NAME ,@initStateFROM    INFORMATION_SCHEMA.COLUMNSWHERE   TABLE_NAME = @tableName;END;WHILE 1 = 1BEGINSET @columnName = ( SELECT TOP 1ColumnNameFROM    TableColumnMaxLenWHERE   TableName = @tableNameAND ColumnMaxLen = @initState);IF @columnName IS NULLBREAK;UPDATE  TableColumnMaxLenSET     ColumnMaxLen = @searchingStateWHERE   TableName = @tableNameAND 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  TableColumnMaxLenSET     ColumnMaxLen = @columnMaxLenWHERE   TableName = @tableNameAND ColumnName = @columnName;     END;SELECT  *
FROM    TableColumnMaxLen
WHERE   TableName = @tableName;

这篇关于MSSQL获取表字段最大长度的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---