How to get Maximum and Minimum Length for each Column Values for all the tables in SQL Server Database - SQL Server: Tips and Tricks - 2 (2017)

SQL Server: Tips and Tricks - 2 (2017)

How to get Maximum and Minimum Length for each Column Values for all the tables in SQL Server Database

Scenario:

Let's say you are working as SQL Server developer / Architect and you need to design tables from existing tables. While creating existing tables nobody thought about correct data types and used NVARCHAR(MAX) or VARCHAR(MAX) , text or even ntext. Your goal is analyze the data and then come up best data types for each column. While analysis you need to know the max length for the values your columns so you can provide max data type length for new table columns.

Solution:

The below script is going to run on entire database and get the maximum and minimum length from each of the column for each of the table and return you at the end. This query can take quite a long time depending upon the number of tables, columns and records.

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

https://3.bp.blogspot.com/-JkHzrgwHQPA/V8dSljXjG7I/AAAAAAAAfyU/PULZcP9ohe0MpPT7PSMxmEUyAeCy2TJswCLcB/s640/Capture.PNG