A simple TSQL script to quickly generate c# POCO classes from SQL Server tables and views. You may tweak the output as needed. Not all datatypes are represented but this should save a bunch of boilerplate coding. USAGE: Run this query against the database of your choice. The script will loop through tables, views and their respective columns. Re…

TSQL-to-POCO

declare @tableName varchar(200)
declare @columnName varchar(200)
declare @nullable varchar(50)
declare @datatype varchar(50)
declare @maxlen int

declare @sType varchar(50)
declare @sProperty varchar(200)

DECLARE table_cursor CURSOR FOR 
SELECT TABLE_NAME
FROM [INFORMATION_SCHEMA].[TABLES]

OPEN table_cursor

FETCH NEXT FROM table_cursor 
INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'public class ' + @tableName + ' {'

    DECLARE column_cursor CURSOR FOR 
    SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, isnull(CHARACTER_MAXIMUM_LENGTH,'-1') 
  from [INFORMATION_SCHEMA].[COLUMNS] 
	WHERE [TABLE_NAME] = @tableName
	order by [ORDINAL_POSITION]

    OPEN column_cursor
    FETCH NEXT FROM column_cursor INTO @columnName, @nullable, @datatype, @maxlen

    WHILE @@FETCH_STATUS = 0
    BEGIN

	-- datatype
	select @sType = case @datatype
	when 'int' then 'Int32'
	when 'decimal' then 'Decimal'
	when 'money' then 'Decimal'
	when 'char' then 'String'
	when 'nchar' then 'String'
	when 'varchar' then 'String'
	when 'nvarchar' then 'String'
	when 'uniqueidentifier' then 'Guid'
	when 'datetime' then 'DateTime'
	when 'bit' then 'Boolean'
	else 'String'
	END

		If (@nullable = 'NO')
			PRINT '[Required]'
		if (@sType = 'String' and @maxLen <> '-1')
			Print '[MaxLength(' +  convert(varchar(4),@maxLen) + ')]'
		SELECT @sProperty = 'public ' + @sType + ' ' + @columnName + ' { get; set;}'
		PRINT @sProperty

		print ''
		FETCH NEXT FROM column_cursor INTO @columnName, @nullable, @datatype, @maxlen
	END
    CLOSE column_cursor
    DEALLOCATE column_cursor

	print '}'
	print ''
    FETCH NEXT FROM table_cursor 
    INTO @tableName
END
CLOSE table_cursor
DEALLOCATE table_cursor

That's a very good observation you made. I missed seeing that double type is not there in the logic.

This T-SQL script is designed to create C# class properties dynamically based on a database schema. It extracts column names, data types, nullability, and descriptions from the database and generates corresponding C# properties with appropriate attributes. The script automatically adds annotations for primary keys, foreign keys, required fields, and maximum string lengths, enhancing the generated C# code's readability and maintainability.

`DECLARE @tableName varchar(200)
DECLARE @columnName varchar(200)
DECLARE @nullable varchar(50)
DECLARE @datatype varchar(50)
DECLARE @maxlen int
DECLARE @description nvarchar(500)
DECLARE @isPrimaryKey bit
DECLARE @isForeignKey bit

DECLARE @Stype varchar(50)
DECLARE @Sproperty varchar(200)

DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME
FROM [INFORMATION_SCHEMA].[TABLES]

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'public class ' + @tableName + ' {'

DECLARE column_cursor CURSOR FOR 
SELECT 
    c.COLUMN_NAME, 
    c.IS_NULLABLE, 
    c.DATA_TYPE, 
    ISNULL(c.CHARACTER_MAXIMUM_LENGTH, '-1') AS MaxLen,
    ISNULL(CAST(ep.value AS NVARCHAR(MAX)), '') AS Description,
    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
    CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey
FROM [INFORMATION_SCHEMA].[COLUMNS] c
LEFT JOIN sys.columns sc ON sc.name = c.COLUMN_NAME 
    AND sc.object_id = OBJECT_ID(@tableName)
LEFT JOIN sys.extended_properties ep ON ep.major_id = sc.object_id 
    AND ep.minor_id = sc.column_id
    AND ep.name = 'MS_Description'
LEFT JOIN (
    SELECT kcu.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
        ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk ON pk.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN (
    SELECT fkc.parent_column_id, fkc.parent_object_id
    FROM sys.foreign_key_columns fkc
) fk ON fk.parent_column_id = sc.column_id 
      AND fk.parent_object_id = OBJECT_ID(@tableName)
WHERE c.TABLE_NAME = @tableName
ORDER BY c.ORDINAL_POSITION

OPEN column_cursor
FETCH NEXT FROM column_cursor 
INTO @columnName, @nullable, @datatype, @maxlen, @description, @isPrimaryKey, @isForeignKey

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 資料型別轉換
    SELECT @sType = CASE @datatype
        WHEN 'int' THEN 'Int32'
        WHEN 'decimal' THEN 'Decimal'
        WHEN 'money' THEN 'Decimal'
        WHEN 'char' THEN 'String'
        WHEN 'nchar' THEN 'String'
        WHEN 'varchar' THEN 'String'
        WHEN 'nvarchar' THEN 'String'
        WHEN 'uniqueidentifier' THEN 'Guid'
        WHEN 'datetime' THEN 'DateTime'
        WHEN 'bit' THEN 'Boolean'
        ELSE 'String'
    END

    -- 輸出 XML 格式的 summary 註解，包含描述、主鍵和外鍵資訊
    PRINT '    /// <summary>'
    PRINT '    /// ' + @columnName
    IF (LEN(@description) > 0)
        PRINT '    /// ' + @description
    IF (@isPrimaryKey = 1)
        PRINT '    /// 主鍵 (Primary Key)'
    IF (@isForeignKey = 1)
        PRINT '    /// 外鍵 (Foreign Key)'
    PRINT '    /// </summary>'

    -- Required 註解
    IF (@nullable = 'NO')
        PRINT '    [Required]'

    -- MaxLength 註解
    IF (@sType = 'String' AND @maxlen <> '-1')
        PRINT '    [MaxLength(' + CONVERT(varchar(4), @maxlen) + ')]'

    -- 屬性輸出
    SELECT @sProperty = '    public ' + @sType + ' ' + @columnName + ' { get; set; }'
    PRINT @sProperty

    PRINT ''
    FETCH NEXT FROM column_cursor 
    INTO @columnName, @nullable, @datatype, @maxlen, @description, @isPrimaryKey, @isForeignKey
END

CLOSE column_cursor
DEALLOCATE column_cursor

PRINT '}'
PRINT ''
FETCH NEXT FROM table_cursor INTO @tableName

END

CLOSE table_cursor
DEALLOCATE table_cursor
`

@joey-qc Thanks for starting this !!

Here is script covers most of the common data types. The final column outputs XML, which is clickable and can be saved directly as a .cs file. Here’s the code.

`

/* Set-based class generator: no cursors, uses CTEs + STRING_AGG
Outputs one row per table with the full C# class text.
*/

DECLARE @TargetTables TABLE (TABLE_SCHEMA sysname, TABLE_NAME sysname);
INSERT INTO @TargetTables(TABLE_SCHEMA, TABLE_NAME)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t;

WITH base_cols AS (
SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
c.ORDINAL_POSITION,
c.IS_NULLABLE,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
sc.object_id,
sc.column_id,
CAST(ep.value AS nvarchar(max)) AS Description,
CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN @TargetTables tt
ON tt.TABLE_SCHEMA = c.TABLE_SCHEMA
AND tt.TABLE_NAME = c.TABLE_NAME
LEFT JOIN sys.columns sc
ON sc.name = c.COLUMN_NAME
AND sc.object_id = OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + N'.' + QUOTENAME(c.TABLE_NAME))
LEFT JOIN sys.extended_properties ep
ON ep.major_id = sc.object_id
AND ep.minor_id = sc.column_id
AND ep.name = N'MS_Description'
LEFT JOIN (
SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
AND tc.TABLE_NAME = kcu.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pk
ON pk.TABLE_SCHEMA = c.TABLE_SCHEMA
AND pk.TABLE_NAME = c.TABLE_NAME
AND pk.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN sys.foreign_key_columns fk
ON fk.parent_object_id = sc.object_id
AND fk.parent_column_id = sc.column_id
),
typed_cols AS (
SELECT
b.*,
CASE b.DATA_TYPE
WHEN 'int' THEN 'Int32'
WHEN 'smallint' THEN 'Int16'
WHEN 'bigint' THEN 'Int64'
WHEN 'tinyint' THEN 'Byte'
WHEN 'decimal' THEN 'Decimal'
WHEN 'numeric' THEN 'Decimal'
WHEN 'money' THEN 'Decimal'
WHEN 'smallmoney' THEN 'Decimal'
WHEN 'float' THEN 'Double'
WHEN 'real' THEN 'Single'
WHEN 'char' THEN 'String'
WHEN 'nchar' THEN 'String'
WHEN 'varchar' THEN 'String'
WHEN 'nvarchar' THEN 'String'
WHEN 'text' THEN 'String'
WHEN 'ntext' THEN 'String'
WHEN 'uniqueidentifier' THEN 'Guid'
WHEN 'datetime' THEN 'DateTime'
WHEN 'smalldatetime' THEN 'DateTime'
WHEN 'datetime2' THEN 'DateTime'
WHEN 'date' THEN 'DateTime'
WHEN 'time' THEN 'TimeSpan'
WHEN 'bit' THEN 'Boolean'
WHEN 'binary' THEN 'Byte[]'
WHEN 'varbinary' THEN 'Byte[]'
WHEN 'image' THEN 'Byte[]'
ELSE 'String'
END AS SType
FROM base_cols b
),
lines AS (
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.COLUMN_NAME,
t.ORDINAL_POSITION,
L.ord AS LineOrderWithinColumn,
L.line AS LineText
FROM typed_cols t
CROSS APPLY (VALUES
(1, N' ///

'),
(2, N' /// ' + t.COLUMN_NAME),
(3, CASE WHEN NULLIF(t.Description, N'') IS NOT NULL
THEN N' /// ' + t.Description ELSE NULL END),
(4, CASE WHEN t.IsPrimaryKey = 1 THEN N' /// (Primary Key)' ELSE NULL END),
(5, CASE WHEN t.IsForeignKey = 1 THEN N' /// (Foreign Key)' ELSE NULL END),
(6, N' ///

'),
(7, CASE WHEN t.IS_NULLABLE = 'NO' THEN N' [Required]' ELSE NULL END),
(8, CASE WHEN t.SType = 'String' AND ISNULL(t.CHARACTER_MAXIMUM_LENGTH, -1) <> -1
THEN N' [MaxLength(' + CONVERT(varchar(10), t.CHARACTER_MAXIMUM_LENGTH) + N')]'
ELSE NULL END),
(9, N' public ' + t.SType + N' ' + t.COLUMN_NAME + N' { get; set; }'),
(10, N'')
) L(ord, line)
WHERE L.line IS NOT NULL
),
class_bodies AS (
SELECT
l.TABLE_SCHEMA,
l.TABLE_NAME,
STRING_AGG(l.LineText, CHAR(10)) WITHIN GROUP (ORDER BY l.ORDINAL_POSITION, l.LineOrderWithinColumn) AS BodyText
FROM lines l
GROUP BY l.TABLE_SCHEMA, l.TABLE_NAME
)
SELECT
cb.TABLE_SCHEMA,
cb.TABLE_NAME,
CAST('public class ' + cb.TABLE_NAME + ' {' + CHAR(13)+CHAR(10)
+ cb.BodyText + CHAR(13)+CHAR(10) + '}' AS XML) AS ClassDefinition
FROM class_bodies cb
ORDER BY cb.TABLE_SCHEMA, cb.TABLE_NAME;

`