Get Maximum and Minimum Length for each Column Values for all the tables in SQL Server Database

With the help of below sql script we will get the min and max length of the column.

DECLARE @DatabaseName VARCHAR(100) DECLARE @SchemaName VARCHAR(100) DECLARE @TableName VARCHAR(100) DECLARE @ColumnName VARCHAR(100) DECLARE @FullyQualifiedTableName VARCHAR(500) DECLARE @DataType VARCHAR(50) --Create Temp Table to Save Results IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results CREATE TABLE #Results ( DatabaseName VARCHAR(100) ,SchemaName VARCHAR(100) ,TableName VARCHAR(100) ,ColumnName VARCHAR(100) ,ColumnDataType VARCHAR(50) ,MaxLength VARCHAR(50) ,MinLength VARCHAR(50) ) DECLARE Cur CURSOR FOR SELECT DB_Name() AS DatabaseName ,s.[name] AS SchemaName ,t.[name] AS TableName ,c.[name] AS ColumnName ,'[' + DB_Name() + ']' + '.[' + s.NAME + '].' + '[' + T.NAME + ']' AS FullQualifiedTableName ,d.[name] AS DataType FROM sys.schemas s INNER JOIN sys.tables t ON s.schema_id = t.schema_id INNER JOIN sys.columns c ON t.object_id = c.object_id INNER JOIN sys.types d ON c.user_type_id = d.user_type_id WHERE d.NAME LIKE '%char%' or d.name like '%Text%' OPEN Cur FETCH NEXT FROM Cur INTO @DatabaseName ,@SchemaName ,@TableName ,@ColumnName ,@FullyQualifiedTableName ,@DataType WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL VARCHAR(MAX) = NULL SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' + @SchemaName + ''' AS SchemaName, ''' + @TableName + ''' AS TableName, ''' + @ColumnName + ''' AS ColumnName, ''' + @DataType + ''' AS ColumnDataType, (Select MAX(LEN(CAST(' + @ColumnName + ' AS NVARCHAR(MAX)))) from ' + @FullyQualifiedTableName + ' with (nolock)) AS MaxLength, (Select MIN(LEN(CAST(' + @ColumnName + ' AS NVARCHAR(MAX)))) from ' + @FullyQualifiedTableName + ' with (nolock)) AS MinLength' PRINT @SQL INSERT INTO #Results EXEC (@SQL) FETCH NEXT FROM Cur INTO @DatabaseName ,@SchemaName ,@TableName ,@ColumnName ,@FullyQualifiedTableName ,@DataType END CLOSE Cur DEALLOCATE Cur SELECT * FROM #Results

See the output.