[Sql Server] Generate Datatype String based on information_schema.columns

Fn_GetDatatypeString.sql

/****** 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