Post reply

Analyze Table structure for best data type

  • December 13, 2016 at 9:43 am

    #338269

    Hi,

    I have a table that was imported from Oracle and all numeric data type were transferred as numeric (38,0). I am looking for a tool\script to analyze the data in each column of a table and recommend an appropriated datatype. For example, a column has a min value of 0 and a max value 1,000,000. Recommendation would be to use an INT datatype.

    Thanks

  • December 13, 2016 at 11:33 am

    #1917707

    This query should help

    DECLARE @SQL VARCHAR(MAX);

    SELECT @SQL = 'WITH cteMinMax AS (' + CHAR(10)

    + ' SELECT' + CHAR(10)

    + STUFF((

    SELECT ' , min' + CONVERT(VARCHAR(20), sc.column_id) + ' = MIN([' + sc.name + ']), max' + CONVERT(VARCHAR(20), sc.column_id) + ' = MAX([' + sc.name + '])' + CHAR(10)

    FROM sys.columns AS sc

    WHERE sc.object_id = OBJECT_ID(N'dbo.Imported', N'U')

    AND sc.user_type_id = 108 -- numeric

    AND sc.precision = 38

    AND sc.scale = 0

    ORDER BY sc.column_id

    FOR XML PATH('')

    ), 1, 6, ' ')

    + ' FROM dbo.Imported' + CHAR(10)

    + ')' + CHAR(10)

    + 'SELECT ColName, minVal, maxVal' + CHAR(10)

    + ' , RecommendedType = CASE

    WHEN minVal IS NULL AND maxVal IS NULL THEN ''NO DATA''

    WHEN minVal >= 0 AND maxVal <= 255 THEN ''TINYINT''

    WHEN minVal >= -32768 AND maxVal <= 32767 THEN ''SMALLINT''

    WHEN minVal >= -2147483648 AND maxVal <= 2147483647 THEN ''INT''

    WHEN minVal >= -9223372036854775808 AND maxVal <= 9223372036854775807 THEN ''BIGINT''

    ELSE ''NUMERIC(38,0)''

    END' + CHAR(10)

    + 'FROM cteMinMax' + CHAR(10)

    + 'CROSS APPLY (' + CHAR(10)

    + ' VALUES' + CHAR(10)

    + STUFF((

    SELECT ' , ( ''' + sc.name + ''', min' + CONVERT(VARCHAR(20), sc.column_id) + ', max' + CONVERT(VARCHAR(20), sc.column_id) + ' )' + CHAR(10)

    FROM sys.columns AS sc

    WHERE sc.object_id = OBJECT_ID(N'dbo.Imported', N'U')

    AND sc.user_type_id = 108 -- numeric

    AND sc.precision = 38

    AND sc.scale = 0

    ORDER BY sc.column_id

    FOR XML PATH('')

    ), 1, 6, ' ')

    + ') x(ColName, minVal, maxVal);' + CHAR(10)

    PRINT (@SQL);

    EXEC (@SQL);

    How to post data/code on a forum to get the best help.

  • December 13, 2016 at 3:46 pm

    #1917750

    Thank you very much. That is exactly what I needed.

Viewing 3 posts - 1 through 2 (of 2 total)

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