Skip to content

Instantly share code, notes, and snippets.

@wcypierre

wcypierre/Fn_GetDatatypeString.sql

Created June 28, 2020 12:18
Show Gist options
  • Save wcypierre/e19f2f99a130a7d82b123a88d6fa97c3 to your computer and use it in GitHub Desktop.
Save wcypierre/e19f2f99a130a7d82b123a88d6fa97c3 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. wcypierre created this gist Jun 28, 2020.
    75 changes: 75 additions & 0 deletions Fn_GetDatatypeString.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,75 @@
    /****** Object: UserDefinedFunction [dbo].[Fn_GetDatatypeString] Script Date: 6/28/2020 6:49:21 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE FUNCTION [dbo].[Fn_GetDatatypeString]
    (
    @INFORMATION_SCHEMA_DATA_TYPE NVARCHAR(MAX),
    @INFORMATION_SCHEMA_CHARACTER_MAXIMUM_LENGTH NVARCHAR(MAX),
    @INFORMATION_SCHEMA_NUMERIC_PRECISION NVARCHAR(MAX),
    @INFORMATION_SCHEMA_NUMERIC_SCALE NVARCHAR(MAX),
    @INFORMATION_SCHEMA_DATETIME_PRECISION NVARCHAR(MAX)
    )
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN
    DECLARE @DATATYPE_STRING NVARCHAR(MAX);

    /* Types without size */
    IF @INFORMATION_SCHEMA_DATA_TYPE = 'int' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'bigint' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'bit' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'date' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'datetime' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'float' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'geography' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'geometry' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'hierarchyid' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'image' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'money' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'ntext' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'real' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'smalldatetime' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'smallint' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'smallmoney' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'sql_variant' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'text' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'timestamp' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'tinyint' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'uniqueidentifier' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'xml'
    BEGIN
    SET @DATATYPE_STRING = @INFORMATION_SCHEMA_DATA_TYPE;
    END
    /* Decimal/Money related - Precision + Scale */
    ELSE IF @INFORMATION_SCHEMA_DATA_TYPE = 'decimal' OR @INFORMATION_SCHEMA_DATA_TYPE = 'numeric'
    BEGIN
    SET @DATATYPE_STRING = @INFORMATION_SCHEMA_DATA_TYPE + '(' + @INFORMATION_SCHEMA_NUMERIC_PRECISION + ',' + @INFORMATION_SCHEMA_NUMERIC_SCALE + ')';
    END
    /* Date/Time related - Precision */
    ELSE IF @INFORMATION_SCHEMA_DATA_TYPE = 'datetime2' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'datetimeoffset' OR
    @INFORMATION_SCHEMA_DATA_TYPE = 'time'
    BEGIN
    SET @DATATYPE_STRING = @INFORMATION_SCHEMA_DATA_TYPE + '(' + @INFORMATION_SCHEMA_DATETIME_PRECISION + ')';
    END
    ELSE
    BEGIN
    IF @INFORMATION_SCHEMA_CHARACTER_MAXIMUM_LENGTH = -1
    BEGIN
    SET @DATATYPE_STRING = @INFORMATION_SCHEMA_DATA_TYPE + '(max)';
    END
    ELSE
    BEGIN
    SET @DATATYPE_STRING = @INFORMATION_SCHEMA_DATA_TYPE + '('+ @INFORMATION_SCHEMA_CHARACTER_MAXIMUM_LENGTH +')';
    END
    END

    RETURN @DATATYPE_STRING
    END

    GO