I needed a way to generate simple classes for some tables across SQL version 2012 to 6.5. Based on Stack Overflow answer from Alex Aza: http://stackoverflow.com/a/5873231/331937

CreateClassFromDBTableSchema

SET STATISTICS IO OFF
SET NOCOUNT ON 

-- =============================================
-- Author:		Shawn Dube, twitter: @shawndube
-- Create date: 4/24/15
--  Based on work by Alex Aza: http://stackoverflow.com/a/5873231/331937
--	Uses:  Initial use was to create C# Classes 
--			from an existing database table schema.
--			May be able to do other templating type
--			things based on structure.
--
--	Notes: Best if run in "Results to Text" , or out to file
--
-- =============================================
--
--	Assume class per table, no inferred inheritance
--
--	want to support various nullable declarations
--	 int? 
--	 Nullable<int>  --c#
--	 Nullable(Of Integer) --VB.net
--	
--	Templates:
--		set @NullableTypeTemplate =   '{{ConvertedType}}?'
--		set @NullableTypeTemplate = 'Nullable<{{ConvertedType}}>' 
--		set @NullableTypeTemplate = 'Nullable(Of {{ConvertedType}})'
-- =============================================


 declare @ClassHeaderTemplate varchar(8000)
 declare @ClassFooterTemplate varchar(8000)
 declare @PropertyTemplate varchar(8000)
 declare @NullableTypeTemplate varchar(8000)
 declare @NonNullableTypeTemplate varchar(8000)

  

set @ClassHeaderTemplate   = '

namespace IDSTC.Enterprise.IdentityManagement.Models
{
	public class {{TABLE_NAME}} : BaseEntity, IAuditableEntity
	{
	 
'

set @PropertyTemplate = '
		public {{ConvertedTypeWithNullability}} {{COLUMN_NAME}} { get; set; }
'

set @NullableTypeTemplate =   '{{ConvertedType}}?'
 

set @NonNullableTypeTemplate = '{{ConvertedType}}'

set @ClassFooterTemplate = '

		}
	}
'
 

--Move conversions to table... would like to be a function BUT i want this script self contained (for now)
declare @TypeConversions  table (
	SQLType			sysname,
	CSharpType		NVARCHAR(4000),
	dotNetType		NVARCHAR(4000),
	SqlDbTypeEnum	NVARCHAR(4000),
	DbTypeEnum		NVARCHAR(4000)
)

insert into @TypeConversions
-- slightly edited table from https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
	select 'bigint',                    'long',                                                  'Int64',                                     'BigInt',                 'Int64'                                Union
	select 'binary',                    'byte[]',                                                'Byte[]',                                    'VarBinary',              'Binary'                               Union
	select 'bit',                       'bool',                                                  'Boolean',                                   'Bit',                    'Boolean'                              Union
	select 'char',                      'string',                                                'String',                                    'Char',                   'AnsiStringFixedLength'                Union
	select 'date',                      'DateTime',                                              'DateTime',                                  'Date',                   'Date'                                 Union
	select 'datetime',                  'DateTime',                                              'DateTime',                                  'DateTime',               'DateTime'                             Union
	select 'datetime2',                 'DateTime',                                              'DateTime',                                  'DateTime2',              'DateTime2'                            Union
	select 'datetimeoffset',            'DateTimeOffset',                                        'DateTimeOffset',                            'DateTimeOffset',         'DateTimeOffset'                       Union
	select 'decimal',                   'decimal',                                               'Decimal',                                   'Decimal',                'Decimal'                              Union
	select 'FILESTREAM',                'byte[]',                                                'Byte[]',                                    'VarBinary',              'Binary'                               Union
	select 'float',                     'float',                                                 'Double',                                    'Float',                  'Double'                               Union
	select 'image',                     'byte[]',                                                'Byte[]',                                    'Binary',                 'Binary'                               Union
	select 'int',                       'int',                                                   'Int32',                                     'Int',                    'Int32'                                Union
	select 'money',                     'decimal',                                               'Decimal',                                   'Money',                  'Decimal'                              Union
	select 'nchar',                     'char',                                                  'String',                                    'NChar',                  'StringFixedLength'                    Union
	select 'ntext',                     'string',                                                'String',                                    'NText',                  'String'                               Union
	select 'numeric',                   'decimal',                                               'Decimal',                                   'Decimal',                'Decimal'                              Union
	select 'nvarchar',                  'string',                                                'String',                                    'NVarChar',               'String'                               Union
	select 'real',                      'double',                                                'Single',                                    'Real',                   'Single'                               Union
	select 'rowversion',                'byte[]',                                                'Byte[]',                                    'Timestamp',              'Binary'                               Union
	select 'smalldatetime',             'DateTime',                                              'DateTime',                                  'DateTime',               'DateTime'                             Union
	select 'smallint',                  'short',                                                 'Int16',                                     'SmallInt',               'Int16'                                Union
	select 'smallmoney',                'decimal',                                               'Decimal',                                   'SmallMoney',             'Decimal'                              Union
	select 'sql_variant',               'object',                                                'Object',                                    'Variant',                'Object'                               Union
	select 'text',                      'string',                                                'String',                                    'Text',                   'String'                               Union
	select 'time',                      'timespan',                                              'TimeSpan',                                  'Time',                   'Time'                                 Union
	select 'timestamp',                 'DateTime',                                              'Byte[]',                                    'Timestamp',              'Binary'                               Union
	select 'tinyint',                   'byte',                                                  'Byte',                                      'TinyInt',                'Byte'                                 Union
	select 'uniqueidentifier',          'Guid',                                                  'Guid',                                      'UniqueIdentifier',       'Guid'                                 Union
	select 'varbinary',                 'byte[]',                                                'Byte[]',                                    'VarBinary',              'Binary'                               Union
	select 'varchar',                   'string',                                                'String',                                    'VarChar',                'AnsiString '                          Union
	select 'xml',                       'xml',                                                   'Xml',                                       'Xml',                    'Xml'


--at first I was using a row_number() over() but then needed to get data from a SQL 6.5 database.
--I realized I can use Ordinal_Position = 1 for the first row but I needed (wanted really) to know the last row for the footer.
declare @TableColumnPositionInfo table(
	Table_Name sysname,
	FirstFieldPosition int,
	LastFieldPosition int
)
 
insert into @TableColumnPositionInfo
select table_name, min(Ordinal_Position) as FirstFieldPosition,  max(Ordinal_Position) as LastFieldPosition from information_schema.columns group by table_name


 
declare @TablesToConvert table(
	Table_Name sysname,
	Table_Order int identity(1,1) --give the user a chance to set the order via the select below
)

insert into @TablesToConvert 
select table_name from information_schema.tables where table_name like '%sale%'  and table_name not like '%wrk' 
order by case when table_name like '%log' then 1 else 0 end, table_name --put all the log tables last





	select 
	 
		--Header
		case when IsFirstField = 1 then replace(coalesce(@ClassHeaderTemplate, ''), '{{TABLE_NAME}}', Table_Name) else '' end +
	
		--Props
		replace(replace(replace(@PropertyTemplate, '{{ConvertedTypeWithNullability}}', ConvertedTypeWithNullability), '{{ConvertedType}}', ConvertedType), '{{COLUMN_NAME}}', COLUMN_NAME) +

		--Footer
		case when IsLastField = 1 then @ClassFooterTemplate else '' end 

	  
	from
	(
		select 
			replace(column_name, ' ', '_') as column_name,
			c.Table_Name,
			Ordinal_Position ,
			case when Ordinal_Position = tcpi.FirstFieldPosition then 1 else 0 end as IsFirstField,
			case when Ordinal_Position = tcpi.LastFieldPosition then 1 else 0 end as IsLastField,
			coalesce(CSharpType, 'UNKNOWN_' + DATA_TYPE) ConvertedType,
			case --//TODO: Move nullable into the @TypeConversions table
				when is_nullable = 'YES' and DATA_TYPE in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
				then @NullableTypeTemplate 
				else @NonNullableTypeTemplate 
			end ConvertedTypeWithNullability,
			ttc.Table_Order
		from information_schema.columns  c
		left join @TypeConversions tc on tc.SQLType = c.DATA_TYPE
		join @TablesToConvert ttc on ttc.Table_name = c.Table_name
		join @TableColumnPositionInfo tcpi on tcpi.Table_Name = c.Table_name
		
		) k
	 

		order by Table_Order, Table_Name, Ordinal_Position