December 13, 2016 at 9:43 am
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
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);
December 13, 2016 at 3:46 pm
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