Skip to content

Instantly share code, notes, and snippets.

@joey-qc
Created September 26, 2013 06:56
Show Gist options
  • Save joey-qc/6710702 to your computer and use it in GitHub Desktop.
Save joey-qc/6710702 to your computer and use it in GitHub Desktop.
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…
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
@hesamkal2009
Copy link

hesamkal2009 commented Jun 4, 2018

This is awesome. Thanks!

@rturluck
Copy link

Yes awesome. Good work. Thanks!

@blackberryec
Copy link

This is awesome

@ancgate
Copy link

ancgate commented Aug 8, 2018

This is a very good and useful script. Thanks

@eddie3716
Copy link

Fantastic script.

@sophanetyut
Copy link

cool

@Fineman22
Copy link

You should add
when 'datetime2' then 'DateTime'

@TalhaRasheed
Copy link

That's so Nice

@TalhaRasheed
Copy link

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] where TABLE_NAME ='EmployeeWorkingDay'

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'public class ' + @tableName + ' {'
PRINT ''
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 'Int'
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

@TalhaRasheed
Copy link

For 1 Table Only

@LHenriquezGitHub
Copy link

Great script, Thank you.

@naeemaei
Copy link

thanks

@fomoruto
Copy link

fomoruto commented Mar 5, 2019

Great!
Thank you. :)

@samuelgutierrezfuentes
Copy link

Beatifult...
Thank you..

@chadkuehn
Copy link

I may toy with this. Interesting. Thanks!

@catinodeh
Copy link

I've made some small changes. I added some missing types and worked a bit with formatting. I also added [Key] to the first property (usually the primary key).

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

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

DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_NAME = 'WorkOrderCategory'

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'), ORDINAL_POSITION 
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, @pos

WHILE @@FETCH_STATUS = 0
BEGIN

-- datatype
select @sType = case @datatype
when 'int' then 'int'
when 'decimal' then 'Decimal'
when 'money' then 'Decimal'
when 'char' then 'string'
when 'nchar' then 'string'
when 'smallint' then 'short'
when 'varchar' then 'string'
when 'nvarchar' then 'string'
when 'uniqueidentifier' then 'Guid'
when 'datetime' then 'DateTime'
when 'bit' then 'bool'
else 'string'
END

	If (@pos = 1)
		PRINT '	[Key]'
	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, @pos
END
CLOSE column_cursor
DEALLOCATE column_cursor

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

END
CLOSE table_cursor
DEALLOCATE table_cursor

@jnewton00
Copy link

OMG! So much time saved. Massive thanks.

@heisdeone
Copy link

heisdeone commented Feb 13, 2020

I have made an addition of some datatypes and also add "?" for nullable fields.

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

declare @Stype varchar(50)
declare @isnullable varchar(1)
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'), ORDINAL_POSITION
	, CASE WHEN (IS_NULLABLE = 'NO') THEN '' 
	ELSE 
		CASE WHEN (DATA_TYPE IN ('char','nchar','varchar','nvarchar')) THEN '' ELSE '?' END
	END

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, @pos, @isNullable

WHILE @@FETCH_STATUS = 0
BEGIN

-- datatype
select @sType = case @datatype
	when 'int' then 'int'
	when 'smallint' then 'short'
	when 'bigint' then 'long'
	when 'decimal' then 'Decimal'
	when 'money' then 'Decimal'
	when 'char' then 'string'
	when 'nchar' then 'string'
	when 'smallint' then 'short'
	when 'varchar' then 'string'
	when 'nvarchar' then 'string'
	when 'uniqueidentifier' then 'Guid'
	when 'datetime' then 'DateTime'
	when 'bit' then 'bool'
else 'string'
END

	If (@pos = 1)
		PRINT '	[Key]'
	If (@nullable = 'NO' AND @pos > 1)
		PRINT '	[Required]'
	if (@sType = 'string' and @maxLen <> '-1')
		PRINT '	[MaxLength(' +  convert(varchar(4),@maxLen) + ')]'
	if (@sType = 'datetime')
		PRINT '	[Column(TypeName = "datetime")]'
	SELECT @sProperty = '	public ' + @sType + @isNullable + ' ' + @columnName + ' { get; set; }'
	PRINT @sProperty

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

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

END
CLOSE table_cursor
DEALLOCATE table_cursor

@pedrosimoes79
Copy link

Thank you very much, life-saving :)

@shallb
Copy link

shallb commented Aug 17, 2020

Fantastic - saved me a ton of time. Works perfectly.

@iamonlysaiful
Copy link

Great

@MeikelLP
Copy link

MeikelLP commented Jan 7, 2022

Hey, some suggestions:

based on https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings

SQL type CLR type
float double
unknown byte[] (most likely)
varbinary(x) byte[]
tinyint byte

@neilg-dev
Copy link

Thank you, very helpful script

@ByYogi
Copy link

ByYogi commented Nov 22, 2023

very helpful

@softgandhi
Copy link

softgandhi commented Mar 12, 2024

Thanks, I made my simplified version without using cursor, for single table:

declare @tableName varchar(200) = 'Compensation'

SELECT 'public class ' + @tableName + ' {'
UNION ALL
SELECT 
    '   '+ COLUMN_NAME + ' ' + case DATA_TYPE
        when 'int' then 'int'
        when 'decimal' then 'decimal'
        when 'money' then 'decimal'
        when 'char' then 'string'
        when 'nchar' then 'string'
        when 'smallint' then 'short'
        when 'varchar' then 'string'
        when 'nvarchar' then 'string'
        when 'uniqueidentifier' then 'Guid'
        when 'datetime' then 'DateTime'
        when 'bit' then 'bool'
        else 'string'
    END 
    + case when IS_NULLABLE = 'YES' and DATA_TYPE in ('int', 'money', 'smalldatetime', 'datetime', 'bit', 'float', 'decimal') then '?' else '' end
    + ' { get; set; }'
from [INFORMATION_SCHEMA].[COLUMNS] 
WHERE [TABLE_NAME] = @tableName
UNION ALL
SELECT '}'

@sun21170
Copy link

sun21170 commented May 10, 2024

This is a real gem for any developer in .Net. Thanks for this.
I have added a few lines of code to the original code, so that a developer can generate POCO for only one table or a comma-delimited list of table names. The new logic is marked by START and END comments in code below. To generate POCO for all tables, simply do not set the variable @tableNameForPOCO.

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

declare @Stype varchar(50)
declare @isnullable varchar(1)
declare @Sproperty varchar(200)

 ---START of new logic to limit to certain tables only---
DECLARE @tableNameForPOCO VARCHAR(200)--limits POCO class generation to only mentioned table(s)
--SET @tableNameForPOCO = 'Table1,Table2,Table3'--@tableNameForPOCO can be null to mean all tables 
                                                --or it can be set to a comma-delimited list of table names
                                                --there should be no space(s) before and after each comma in this list
DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME
FROM [INFORMATION_SCHEMA].[TABLES] WHERE  CASE
	WHEN ISNULL(@tableNameForPOCO, TABLE_NAME) LIKE ('%,' + TABLE_NAME + ',%') THEN 1
	WHEN ISNULL(@tableNameForPOCO, TABLE_NAME) LIKE (TABLE_NAME + ',%') THEN 1
	WHEN ISNULL(@tableNameForPOCO, TABLE_NAME) LIKE ('%,' + TABLE_NAME) THEN 1
	WHEN ISNULL(@tableNameForPOCO, TABLE_NAME) LIKE TABLE_NAME THEN 1
	ELSE 0
END = 1
 ---END of new logic to limit to certain tables only---

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'), ORDINAL_POSITION
	, CASE WHEN (IS_NULLABLE = 'NO') THEN '' 
	ELSE 
		CASE WHEN (DATA_TYPE IN ('char','nchar','varchar','nvarchar')) THEN '' ELSE '?' END
	END

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, @pos, @isNullable

WHILE @@FETCH_STATUS = 0
BEGIN

-- datatype
select @sType = case @datatype
	when 'int' then 'int'
	when 'smallint' then 'short'
	when 'bigint' then 'long'
	when 'decimal' then 'Decimal'
	when 'money' then 'Decimal'
	when 'char' then 'string'
	when 'nchar' then 'string'
	when 'smallint' then 'short'
	when 'varchar' then 'string'
	when 'nvarchar' then 'string'
	when 'uniqueidentifier' then 'Guid'
	when 'datetime' then 'DateTime'
	when 'bit' then 'bool'
else 'string'
END

	If (@pos = 1)
		PRINT '	[Key]'
	If (@nullable = 'NO' AND @pos > 1)
		PRINT '	[Required]'
	if (@sType = 'string' and @maxLen <> '-1')
		PRINT '	[MaxLength(' +  convert(varchar(4),@maxLen) + ')]'
	if (@sType = 'datetime')
		PRINT '	[Column(TypeName = "datetime")]'
	SELECT @sProperty = '	public ' + @sType + @isNullable + ' ' + @columnName + ' { get; set; }'
	PRINT @sProperty

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

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

@dscaravaggi
Copy link

Thanks a lot,
I added Float Double management plus KEY

alter procedure usp_generate @tableName_arg varchar(200)
as
/*
example
  exec usp_generate @tableName_arg = 'P_SECONDO'
*/
BEGIN
	set nocount on;

    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 @IsKey int;
	declare @col_ordinal int;


DECLARE table_cursor CURSOR FAST_FORWARD FOR 
	SELECT TABLE_NAME
	FROM [INFORMATION_SCHEMA].[TABLES] T
	where t.TABLE_TYPE = 'BASE TABLE'
	and not ( TABLE_NAME LIKE 'sys%' or TABLE_NAME LIKE 'MSMerg%'  OR TABLE_NAME LIKE 'MSRepl%' OR  TABLE_NAME LIKE 'MSDynam%'  )
	and TABLE_NAME = @tableName_arg
	order by TABLE_NAME asc;

OPEN table_cursor

FETCH NEXT FROM table_cursor 
INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'public class ' + @tableName + ' {'
PRINT '';
PRINT '    #region column_scaffolding';

PRINT '';


    DECLARE column_cursor CURSOR FAST_FORWARD FOR 
		with cte_pk as (
				SELECT Col.Column_Name  from 
				INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
				INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
			WHERE 
				Col.Constraint_Name = Tab.Constraint_Name
				AND Col.Table_Name = Tab.Table_Name
				AND Tab.Constraint_Type = 'PRIMARY KEY'
				AND Col.Table_Name = @tableName
		)
			SELECT C.COLUMN_NAME, IS_NULLABLE, DATA_TYPE, isnull(CHARACTER_MAXIMUM_LENGTH,'-1') ,
			C.ORDINAL_POSITION, case when cte_pk.Column_Name is not null then 1 else 0 end as PK
			--, C.*
		  from [INFORMATION_SCHEMA].[COLUMNS] C
		  left outer join cte_pk
		  on C.COLUMN_NAME = cte_pk.Column_Name
			WHERE [TABLE_NAME] = @tableName
			order by C.[ORDINAL_POSITION];


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

    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'
	when 'float' then 'Double'
	else 'String'
	END

	    If (@IsKey = 1)
			PRINT '    [Key]';
		If (@nullable = 'NO')
			PRINT '    [Required]'
		    raiserror('    [Column(Order=%d)]',10,0,@col_ordinal);
		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,  @col_ordinal, @IsKey
	END
    CLOSE column_cursor
    DEALLOCATE column_cursor

	PRINT '    #endregion';
	print ''
	print '}'
	print ''
    FETCH NEXT FROM table_cursor 
    INTO @tableName
END
CLOSE table_cursor
DEALLOCATE table_cursor;

END;

output example:

public class P_SECONDO {
 
    #region column_scaffolding
 
    [Key]
    [Required]
    [Column(Order=1)]
    public Int32 id { get; set;}
 
    [Column(Order=2)]
    [MaxLength(30)]
    public String desc1 { get; set;}
 
    [Required]
    [Column(Order=3)]
    public Guid rowguid { get; set;}
 
    [Column(Order=4)]
    public Decimal my_decimal { get; set;}
 
    [Column(Order=5)]
    public Double my_float { get; set;}
 
    #endregion
 
}
 

@sun21170
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment