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 @default VARCHAR(50)
DECLARE @defaultExp VARCHAR(50)
DECLARE @referenceTable VARCHAR(50)

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') , COLUMN_DEFAULT
		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, @default

    WHILE @@FETCH_STATUS = 0
    BEGIN

     -- datatype
     SELECT @sType = CASE @datatype
		 WHEN 'tinyint' THEN 'byte'
		 WHEN 'smallint' THEN 'short'
		 WHEN 'int' THEN 'int'
		 WHEN 'bigint' THEN 'long'
		 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 'date' THEN 'DateTime'
		 WHEN 'datetime' THEN 'DateTime'
		 WHEN 'smalldatetime' THEN 'DateTime'
		 WHEN 'bit' THEN 'bool'
		 WHEN 'float' THEN 'float'
		 ELSE 'string'
	 END


           Print '[Display(Name="[[[' +  dbo.fnSplitPascalCase(@columnName) + ']]]")]'
           
                
           IF (@sType = 'string' AND @maxLen <> '-1')
                Print '[MaxLength(' +  CONVERT(VARCHAR(4),@maxLen) + ')]'

           SELECT @defaultExp = @default 
           SELECT @defaultExp = REPLACE(@defaultExp, '(', '')
           SELECT @defaultExp = REPLACE(@defaultExp, ')', '')
           SELECT @defaultExp = REPLACE(@defaultExp, '''', '"')

           IF (@defaultExp IS NULL OR @defaultExp = 'NULL')
                SELECT @defaultExp = ''
           
           IF (@sType = 'bool')
                SELECT @defaultExp = CASE WHEN @defaultExp = '1' THEN 'true' ELSE 'false' END

           IF (@defaultExp = 'getdate')
                SELECT @defaultExp = 'DateTime.Now'

           IF (@defaultExp <> '')
                SELECT @defaultExp = ' = ' + @defaultExp + ';'

           IF (@sType = 'string' )
                PRINT '[DisplayFormat(ConvertEmptyStringToNull = false)]'

           IF (@nullable = 'NO')
                IF (@sType = 'string' )
                     PRINT '[Required(AllowEmptyStrings = true)]'
                ELSE IF (@default IS NULL)
                     PRINT '[Required]'

           SELECT @referenceTable = NULL

           SELECT top 1 @referenceTable = 
                     KCU2.TABLE_NAME 
                FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
                     ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
                     AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
                     AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
                     ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
                     AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
                     AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
                     AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 

			WHERE KCU1.TABLE_NAME = @tableName
				AND KCU1.COLUMN_NAME = @columnName

           IF (@referenceTable IS NOT NULL)
                print '[UIHint(nameof(' + @referenceTable + '))]'

           SELECT @sProperty = 'public ' + @sType + ' ' + @columnName + ' { get; set;}' + @defaultExp
           
		   PRINT @sProperty
           PRINT ''
		   
           FETCH NEXT FROM column_cursor INTO @columnName, @nullable, @datatype, @maxlen, @default
     END
    CLOSE column_cursor
    DEALLOCATE column_cursor

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