How to create a stored procedure in SQL Server that can determine the best data types for all the columns in a table?

Question

Closed. This question needs to be more focused. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65833536/how-to-create-a-stored-procedure-in-sql-server-that-can-determine-the-best-data

Answer 1

Not a fun place to be and I have been there myself. Depending on your version of SQL SERVER (TRY_PARSE requires min 2012), you could try something like this:

DECLARE @UntypedData table ( nvarchar_value nvarchar(255), id int IDENTITY (1,1) );
INSERT INTO @UntypedData VALUES
    ( '1000' ), ( '1000.00' ), ( '12/31/2020' ), ( '2020-12-31 11:59:23' );

SELECT
    nvarchar_value,
    CASE
        WHEN CHARINDEX( '.', nvarchar_value ) > 0 AND TRY_PARSE( nvarchar_value AS decimal ) IS NOT NULL THEN 'decimal'
        WHEN TRY_PARSE( nvarchar_value AS int ) IS NOT NULL THEN 'int'
        WHEN CHARINDEX( ':', nvarchar_value ) > 0 AND TRY_PARSE( nvarchar_value AS datetime ) IS NOT NULL THEN 'datetime'
        WHEN TRY_PARSE( nvarchar_value AS date ) IS NOT NULL THEN 'date'
        ELSE 'string'
    END AS suspected_data_type
FROM @UntypedData ORDER BY id;

Returns

+---------------------+---------------------+
|   nvarchar_value    | suspected_data_type |
+---------------------+---------------------+
| 1000                | int                 |
| 1000.00             | decimal             |
| 12/31/2020          | date                |
| 2020-12-31 11:59:23 | datetime            |
+---------------------+---------------------+

This is just an idea to check for datatypes as there's so much you need to consider with a task like this ( string length, decimal size, etc ). For starters though, do not limit your type detection to a single row. I personally would test all rows before changing their datatype--or at least enough to be confident.