Suggest Datatype

  • 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 🙂

  • 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’! **
  • 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

  • nothing I know of natively in SQL Server. Haven't see a script that could do this either.

  • 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

  • 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

  • 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

  • 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.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • 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.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Viewing 9 posts - 1 through 8 (of 8 total)

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