How to get Min and Max Length for each Column Values for all the tables in SQL Server

In this post we will see how to find minimum and maximum length for each columns for all the tables in SQL Server.

Choosing Data Types 

Choosing a poor data type can have a significant impact on the database design and performance. Developers and DBAs can improve database performance by understanding the data types supported by SQL Server and the implications of choosing different ones. A best practice is to “right size” data types by asking business questions and determining the data types that are best suited to the organization’s and application’s needs.


Right-sizing can result in tremendous storage savings, which can lead to faster database performance. Other considerations to keep in mind are SQL Server’s 8KB data page size limit and the conditions that can lead to page splitting. Also watch out for implicit conversions, an unfortunate by-product of mismatching data types. Taking steps to avoid mismatches and page splitting can greatly enhance performance.

Scenario

Let's say there is a 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 @dbName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @tblName VARCHAR(100)
DECLARE @colName VARCHAR(100)
DECLARE @fqtblName VARCHAR(500)
DECLARE @dataType VARCHAR(50)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#tempData') IS NOT NULL DROP TABLE #tempData
CREATE TABLE #tempData
(
       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
@dbName, @SchemaName, @tblName, @colName, @fqtblName, @dataType

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL VARCHAR(MAX) = NULL
SET @SQL =
'Select '''+@dbName+'''AS DatabaseName,'''+@SchemaName+'''AS SchemaName,'''+@tblName+'''AS TableName,'''+@colName+'''AS ColumnName,'''+@dataType+'''AS ColumnDataType, (Select MAX(LEN (CAST('+@colName+' AS NVARCHAR(MAX)))) from '+@fqtblName+ ' with (nolock)) AS MaxLength,(Select MIN(LEN(CAST('+@colName+' AS NVARCHAR(MAX)))) from '+@fqtblName +' with (nolock)) AS MinLength'

--PRINT @SQL

INSERT INTO #tempData
EXEC (@SQL)  

FETCH NEXT FROM Cur INTO
@dbName, @SchemaName, @tblName, @colName, @fqtblName, @dataType
END

CLOSE Cur
DEALLOCATE Cur

SELECT *FROM #tempData

No comments:

Post a Comment