September 5, 2013 at 2:21 pm
Worked on a new clients data today and saw the columns were all varchar(255) for all the tables.
Quickly scanning thru some of the columns I can clearly see this is incorrect.
Probably imported from text with no data structure.
Does anyone have code that would go thru columns and the data and suggest a datatype
Thought this will be an interesting one to do 🙂
September 5, 2013 at 2:49 pm
You need to look for a sort of data-analyzing tool. I know ERWIN (http://erwin.com/products/data-modeler/standard-edition) has the ability but this is a rather expensive application. Maybe you can download a trial version or find some freeware applications on the internet.
September 5, 2013 at 3:19 pm
Thanks HanShi
I have a an application that can do this Dataflux
Just thought it would be interesting to find out if at all possible in Sql without getting 3rd party tools
But thanks for your reply
September 5, 2013 at 3:39 pm
nothing I know of natively in SQL Server. Haven't see a script that could do this either.
September 5, 2013 at 5:36 pm
Since you are on SQL 2012, I wonder if something in the new Data Quality Services stack can do what you need. No idea about that personally.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 5, 2013 at 5:37 pm
Actually, doesn't SSIS interrogate some amount of rows (configurable?) and have the ability to "suggest" data types for inputs when you are building out transforms?? I am pretty sure I have seen that...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 9, 2013 at 1:40 am
SSIS does have data type suggestions but only in design mode iirc. Once you setup your metadata it's set.
You could do some of the checks yourself using the new TRY_CAST and TRY_PARSE functions to see if a particular column contained data that would be acceptable if you tried altering the data type.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 9, 2013 at 3:17 am
I do have an extremely heavy work in progress that may be of some help.
Haven't looked at it in a while as it was giving me headaches.
The idea is to analyze datatypes based on any combination of:
Server name
Database name
Table name
Column name
data type
Finds min and max values or length and returns suggested data types based on that.
Will also display the space requirement difference.
Some big assumptions are made.
Although it's based on my pre-collected table, column, data type tables, it shouldn't be too hard to tweak to only use the current databases only.
September 9, 2013 at 3:59 am
Actually after rereading my code, it does NOT examine TEXT data and give a more appropriate data type.
Only suggest similar data types for text. I.e. Text to ==>> char, nchar, varchar or nvarchar.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply