How can I check actual column datatype in SQL Server-CodePudding

I would like to check actual datatype of all columns in a database and compare the result with the table information_schema.columns. The aim is to detect oversized column data-type to reduce it.

For example if a column is defined as varchar(255) but all values as only 10 characters or less I would like to see it in the result.

I know that this query is to get the info on each column :

SELECT *
FROM information_schema.columns
WHERE table_schema = 'dbo'
ORDER BY TABLE_NAME, ORDINAL_POSITION

Is anybody knows how can I calculate the maximum value for any data-type and compare it to the setting?

CodePudding user response：

The script below executes a MAX DATALENGTH query against each variable-length column to determine the actual max size (in bytes) and lists the results along with the defined max size. This version uses the SQL Server system catalog views instead of INFORMATION_SCHEMA.

Be aware this can take some time if your database is large and impact performance. If you run this against an active database without RCSI, consider READ_UNCOMMITTED with the understanding the results may not be as accurate.

SET NOCOUNT ON;
DECLARE @results TABLE(
     SchemaName sysname NOT NULL
    ,TableName sysname NOT NULL
    ,ColumnName sysname NOT NULL
    ,DefinedMaxLength NOT int
    ,ActualMaxLength int NULL
);

DECLARE
     @SchemaName sysname
    ,@TableName sysname
    ,@ColumnName sysname
    ,@DefinedMaxLength int
    ,@ActualMaxLength int
    ,@MaxLengthQuery nvarchar(MAX);

DECLARE MaxColumnSizes CURSOR LOCAL FAST_FORWARD FOR
    SELECT 
          OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
         ,OBJECT_NAME(t.object_id) AS TableName
         ,c.name AS ColumnName
         ,CASE WHEN c.max_length = -1 THEN 2147483647 ELSE c.max_length END AS DefinedMaxMength
         ,N'SELECT @ActualMaxLength = MAX(DATALENGTH('   c.name   N'))'
           N' FROM '   QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id))   N'.'   QUOTENAME(OBJECT_NAME(t.object_id)) AS MaxLengthQuery
    FROM sys.tables AS t
    JOIN sys.columns AS c ON c.object_id = t.object_id
    JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.system_type_id = c.user_type_id
    WHERE 
        ty.name IN(N'varchar', 'nvarchar', N'varbinary')
        AND OBJECTPROPERTY(t.object_id, 'IsMSShipped') = 0;

OPEN MaxColumnSizes;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM MaxColumnSizes INTO
         @SchemaName
        ,@TableName
        ,@ColumnName
        ,@DefinedMaxLength
        ,@MaxLengthQuery;
    IF @@FETCH_STATUS = -1 BREAK;
    --PRINT @MaxLengthQuery; --for debugging

    SET @ActualMaxLength = NULL;
    EXEC sp_executesql @MaxLengthQuery, N'@ActualMaxLength int OUTPUT', @ActualMaxLength = @ActualMaxLength OUTPUT;
    INSERT INTO @results (SchemaName, TableName, ColumnName, DefinedMaxLength, ActualMaxLength) 
        VALUES(@SchemaName, @TableName, @ColumnName, @DefinedMaxLength, @ActualMaxLength);
    
END;
CLOSE MaxColumnSizes;
DEALLOCATE MaxColumnSizes;

SELECT 
     SchemaName
    ,TableName
    ,ColumnName
    ,DefinedMaxLength
    ,ActualMaxLength
FROM @results
ORDER BY
     SchemaName
    ,TableName
    ,ColumnName;
GO