TSQL script to generate POCO/DTO from database table

generate_dto.sql

DECLARE @tableName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @className NVARCHAR(MAX)
 
--------------- Input arguments ---------------
SET @tableName = 'MyTableName'
SET @schemaName = 'dbo'
SET @className = @tableName + 'Dto'
--------------- Input arguments end -----------

DECLARE tableColumns CURSOR LOCAL FOR
SELECT  cols.name, cols.system_type_id, cols.is_nullable FROM sys.columns cols
	JOIN sys.tables tbl ON cols.object_id = tbl.object_id    -- use this for tables
	-- join sys.views tbl on cols.object_id = tbl.object_id  -- use this for views
	WHERE tbl.name = @tableName
 
PRINT 'public class ' + @className
PRINT '{'
 
OPEN tableColumns
DECLARE @name NVARCHAR(MAX), @typeId INT, @isNullable BIT, @typeName NVARCHAR(MAX)
FETCH NEXT FROM tableColumns INTO @name, @typeId, @isNullable
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @typeName =
	CASE @typeId
	    WHEN 35 THEN 'string' -- text
		WHEN 36 THEN 'Guid'
		WHEN 42 THEN 'DateTime'
		WHEN 43 THEN 'DateTimeOffset'
		WHEN 52 THEN 'short'
		WHEN 56 THEN 'int'
		WHEN 60 THEN 'decimal' -- MONEY
		WHEN 61 THEN 'DateTime'
		WHEN 62 THEN 'float'
		WHEN 104 THEN 'bool'		
		WHEN 106 THEN 'decimal'
		WHEN 108 THEN 'decimal'
		WHEN 167 THEN 'string'
		WHEN 173 THEN 'byte[]'
		WHEN 189 THEN 'byte[]'
		WHEN 231 THEN 'string'
		WHEN 239 THEN 'string'
		WHEN 241 THEN 'XElement'
		
		ELSE 'TODO(' + CAST(@typeId AS NVARCHAR) + ')'
	END;
	IF @isNullable = 1 AND @typeId != 231 AND @typeId != 239 AND @typeId != 241 and  @typeId != 167
		SET @typeName = @typeName + '?'
	PRINT '    public ' + @typeName + ' ' + @name + ' { get; set; }'	
	FETCH NEXT FROM tableColumns INTO @name, @typeId, @isNullable
END
 
PRINT '}'
 
CLOSE tableColumns