Column Size Checker

  • r5d4

    SSCrazy

    Points: 2499

    Comments posted to this topic are about the item Column Size Checker

  • erikb68

    SSC Rookie

    Points: 25

    It would be usefull if it also included non-text columns. That way one could easilty see the max row size, which can be an issue in for wide denormalized tables used in data marts etc.

  • tomasiranek

    SSC-Addicted

    Points: 429

    It is good script, but it does not work with old SQL2000 data types like TEXT and NTEXT - and it may be usefull to check these fields as there may be lots of conversions from SQL2000 still comming.

  • DougGifford

    SSCommitted

    Points: 1705

    tomasiranek (4/13/2010)


    It is good script, but it does not work with old SQL2000 data types like TEXT and NTEXT - and it may be usefull to check these fields as there may be lots of conversions from SQL2000 still comming.

    I agree, there are still those of us that are still using SQL2000.

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • chris-624650

    Grasshopper

    Points: 19

    Thank you very much for this script. It almost did everything i needed.

    I butchered it a bit as I needed a couple of things added.

    Number Of Distinct Values in a column

    Number Of Null Rows

    Probably not the most elegant way but thought i would post it incase someone else needed it.

    It helps when analysing a db your not familiar with as to what columns are used and what are not.

    /*

    Script : Column Size Checker

    Version : 1.0 (March 2010)

    Author : Richard Doering

    Web : http://sqlsolace.blogspot.com

    */

    SET NOCOUNT ON

    SET ANSI_WARNINGS ON

    DECLARE @SCHEMA VARCHAR(50)

    DECLARE @TABLE VARCHAR(50)

    SET @SCHEMA = ''

    SET @TABLE = ''

    DECLARE @CURRENTROW INT

    DECLARE @TOTALROWS INT

    DECLARE @COLUMNMAXSIZE INT

    DECLARE @COLUMNMINSIZE INT

    DECLARE @DISTINCT_COL_VAL INT

    DECLARE @COUNT_COL_NULL INT

    DECLARE @SQLSTRING NVARCHAR(MAX)

    DECLARE @SQLSTRINGCOL NVARCHAR(MAX)

    DECLARE @SQLSTRINGCOLNULL NVARCHAR(MAX)

    DECLARE @PARAMETER NVARCHAR(500);

    DECLARE @PARAMETERCOL NVARCHAR(500);

    DECLARE @PARAMETERCOLNULL NVARCHAR(500);

    DECLARE @TABLEDETAILS

    TABLE(UNIQUEROWID INT IDENTITY ( 1,1 ),

    TABLE_SCHEMA VARCHAR(255),

    TABLE_NAME VARCHAR(255),

    COLUMN_NAME VARCHAR(255),

    COLUMN_TYPE VARCHAR(255),

    TABLE_ROWS BIGINT,

    MAX_LENGTH INT,

    DATA_MIN_LENGTH INT,

    DATA_MAX_LENGTH INT,

    DISTINCT_COL_VAL INT,

    COUNT_NULLS INT)

    INSERT INTO @TABLEDETAILS

    (TABLE_SCHEMA,

    TABLE_NAME,

    COLUMN_NAME,

    COLUMN_TYPE,

    TABLE_ROWS,

    MAX_LENGTH)

    SELECT SCHEMA_NAME(O.SCHEMA_ID) AS TABLE_SCHEMA,

    OBJECT_NAME(O.OBJECT_ID) AS TABLE_NAME,

    C.NAME AS COLUMN_NAME,

    T.NAME AS COLUMN_TYPE,

    R.SUMROWS AS TABLE_ROWS,

    C.MAX_LENGTH

    FROM SYS.TABLES O

    INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = O.OBJECT_ID

    INNER JOIN SYS.TYPES T ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID AND T.NAME IN ('CHAR','VARCHAR','NCHAR','NVARCHAR')

    INNER JOIN (SELECT OBJECT_ID, SUM(ROWS) AS SUMROWS

    FROM SYS.PARTITIONS

    WHERE INDEX_ID IN (0,1) GROUP BY OBJECT_ID) R ON R.OBJECT_ID = O.OBJECT_ID

    WHERE SCHEMA_NAME(O.SCHEMA_ID) <> 'sys'

    AND OBJECT_NAME(O.OBJECT_ID) = CASE WHEN @TABLE = '' THEN OBJECT_NAME(O.OBJECT_ID) ELSE @TABLE END

    AND SCHEMA_NAME(O.SCHEMA_ID) = CASE WHEN @SCHEMA = '' THEN SCHEMA_NAME(O.SCHEMA_ID) ELSE @SCHEMA END

    SELECT @TOTALROWS = COUNT(*) FROM @TABLEDETAILS

    SELECT @CURRENTROW = 1

    WHILE @CURRENTROW <= @TOTALROWS

    BEGIN

    SET @COLUMNMAXSIZE = 0

    SET @COLUMNMINSIZE = 0

    SELECT @SQLSTRING = 'SELECT @COLUMNSIZEMIN = MIN(LEN([' + COLUMN_NAME + '])) ,@COLUMNSIZEMAX = MAX(LEN([' + COLUMN_NAME + '])) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WITH (NOLOCK)' FROM @TABLEDETAILS WHERE UNIQUEROWID = @CURRENTROW

    SET @PARAMETER = N'@COLUMNSIZEMIN INT OUTPUT,@COLUMNSIZEMAX INT OUTPUT';

    EXECUTE SP_EXECUTESQL @SQLSTRING

    , @PARAMETER

    , @COLUMNSIZEMIN = @COLUMNMINSIZE OUTPUT

    , @COLUMNSIZEMAX = @COLUMNMAXSIZE OUTPUT

    UPDATE @TABLEDETAILS

    SET DATA_MAX_LENGTH = ISNULL(@COLUMNMAXSIZE,0) , DATA_MIN_LENGTH = ISNULL(@COLUMNMINSIZE,0)

    WHERE UNIQUEROWID = @CURRENTROW

    /* Add Count Distinct Values In Columns */

    SET @DISTINCT_COL_VAL = 0

    SELECT @SQLSTRINGCOL = 'SELECT @DISTINCT_COL_VAL_0 = COUNT(DISTINCT[' + COLUMN_NAME + ']) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' FROM @TABLEDETAILS WHERE UNIQUEROWID = @CURRENTROW

    SET @PARAMETERCOL = N'@DISTINCT_COL_VAL_0 INT OUTPUT';

    EXECUTE SP_EXECUTESQL @SQLSTRINGCOL

    , @PARAMETERCOL

    , @DISTINCT_COL_VAL_0 = @DISTINCT_COL_VAL OUTPUT

    UPDATE @TABLEDETAILS

    SET DISTINCT_COL_VAL = ISNULL(@DISTINCT_COL_VAL,0)

    WHERE UNIQUEROWID = @CURRENTROW

    /* End Add Count Distinct Values In Columns */

    /* Add Count Null Values In Columns */

    SET @COUNT_COL_NULL = 0

    --select count(CASE WHEN REPORTINGGROUP1CODE IS NULL THEN 1 END) from M_TYPEAG WHERE REPORTINGGROUP1CODE IS NULL

    SELECT @SQLSTRINGCOLNULL = 'SELECT @COUNT_COL_NULL_0 = COUNT(CASE WHEN [' + COLUMN_NAME + '] IS NULL THEN 1 END) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] IS NULL' FROM @TABLEDETAILS WHERE UNIQUEROWID = @CURRENTROW

    SET @PARAMETERCOLNULL = N'@COUNT_COL_NULL_0 INT OUTPUT';

    EXECUTE SP_EXECUTESQL @SQLSTRINGCOLNULL

    , @PARAMETERCOLNULL

    , @COUNT_COL_NULL_0 = @COUNT_COL_NULL OUTPUT

    UPDATE @TABLEDETAILS

    SET COUNT_NULLS = ISNULL(@COUNT_COL_NULL,0)

    WHERE UNIQUEROWID = @CURRENTROW

    /* End Count Null Values In Columns */

    SET @CURRENTROW = @CURRENTROW + 1

    END

    SELECT TABLE_SCHEMA

    ,TABLE_NAME

    ,TABLE_ROWS

    ,COLUMN_NAME

    ,COLUMN_TYPE

    ,CASE MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(CHAR(10),MAX_LENGTH) END AS COLUMN_MAX_LENGTH

    ,DATA_MIN_LENGTH

    ,DATA_MAX_LENGTH

    ,DISTINCT_COL_VAL

    ,COUNT_NULLS

    FROM @TABLEDETAILS

    ORDER BY 1, 2, 3

  • russella0-96423

    SSC-Addicted

    Points: 448

    Found this very useful today , wasn't able to connect remotely to do a data profile and the network speed would have prevented that anyway so this really helped.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply