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