TSQL script to generate POCO/DTO from database table

generate_dto.sql

-- JS-TS --
-----------
DECLARE @tableName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @className NVARCHAR(MAX)
 
--------------- Input arguments ---------------
SET @tableName = 'TableName'
SET @schemaName = 'dbo'
SET @className = 'TableNameDTO'
--------------- Input arguments end -----------
-- Use sys.views instead of sys.tables for Views

DECLARE tableColumns CURSOR LOCAL FOR
SELECT cols.name, cols.system_type_id, cols.max_length, cols.is_nullable FROM sys.columns cols
	JOIN sys.tables tbl ON cols.object_id = tbl.object_id
	WHERE tbl.name = @tableName

PRINT 'import { IsNotEmpty, IsNumber, MaxLength } from "class-validator";' 
PRINT 'import { Expose } from "class-transformer";'
PRINT ' '
PRINT 'export class ' + @className + ' {'
 
OPEN tableColumns
DECLARE @name NVARCHAR(MAX), @typeId INT, @maxLength INT, @isNullable BIT, @typeName NVARCHAR(MAX)
FETCH NEXT FROM tableColumns INTO @name, @typeId, @maxLength, @isNullable
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @typeName =
	CASE @typeId
		--WHEN 36 THEN 'Guid'
		WHEN 56 THEN 'number'--'int'
		WHEN 59 THEN 'number'--'decimal'
		WHEN 106 THEN 'number'--'double'
		WHEN 108 THEN 'number' --numeric (9,0)
		WHEN 61 THEN 'Date'--'DateTime'
		WHEN 104 THEN 'boolean'--'bool'
		WHEN 231 THEN 'string'
		WHEN 239 THEN 'string'
		WHEN 167 THEN 'string'
		WHEN 175 THEN 'string' -- char ()
		--WHEN 241 THEN --'XElement'
		ELSE 'TODO(' + CAST(@typeId AS NVARCHAR) + ')'
	END;

	IF @isNullable = 0
		PRINT '  @IsNotEmpty()'
	IF @typeId = 56 OR @typeId = 59 OR @typeId = 106
		PRINT '  @IsNumber()'
	IF @typeId = 231 OR @typeId = 239 OR @typeId = 167 OR @typeId = 175
	BEGIN
		IF @MaxLength > 0
		BEGIN
			SET @MaxLength = (@maxLength/ 2)
		END
		PRINT '  @MaxLength('+CAST(@maxLength AS nvarchar)+')'
	END
	PRINT '  @Expose()'
	PRINT '  '+ @name +': '+ @typeName + ';'
	PRINT ''
	FETCH NEXT FROM tableColumns INTO @name, @typeId, @maxLength, @isNullable
END
 
PRINT '}'
 
CLOSE tableColumns