Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Suggest Datatype Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 2:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 2:25 AM
Points: 2, Visits: 101
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
Post #1491945
Posted Thursday, September 5, 2013 2:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 2,224, Visits: 2,657
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1491953
Posted Thursday, September 5, 2013 3:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 2:25 AM
Points: 2, Visits: 101
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
Post #1491968
Posted Thursday, September 5, 2013 3:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:30 PM
Points: 33,055, Visits: 15,167
nothing I know of natively in SQL Server. Haven't see a script that could do this either.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1491974
Posted Thursday, September 5, 2013 5:36 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #1492004
Posted Thursday, September 5, 2013 5:37 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #1492005
Posted Monday, September 9, 2013 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1492677
Posted Monday, September 9, 2013 3:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
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.






For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1492700
Posted Monday, September 9, 2013 3:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
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.






For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1492715
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse