Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
[Sql Server] Generate Datatype String based on information_schema.columns
/****** 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment