@".NET Developer, ERP and
     Database Analyst"

SQL: Create VB.NET Classes from Table Structure

This is the VB.NET version – download here: Generate Classes from Tables or copy/paste from below. C# version is here.

-- Variables.
DECLARE @TabChar AS VARCHAR(1)
DECLARE @Name AS VARCHAR(256)
DECLARE @Column AS VARCHAR(256)
DECLARE @Type AS VARCHAR(256)
DECLARE @Columns AS VARCHAR(max)
DECLARE @Tables AS table(info VARCHAR(max))

-- Cursor to query table names.
DECLARE TableCursor CURSOR FOR
SELECT
	name
FROM sys.tables
WHERE
	type_desc = 'USER_TABLE'
ORDER BY
	name ASC

-- Loop through each table name.
OPEN TableCursor
GOTO FetchNextTable
WHILE @@FETCH_STATUS = 0
	BEGIN

			
			DECLARE @Result VARCHAR(max) = 'Public Class ' + @Name + '
			'

			SELECT @Result = @Result + '
				Private ' + ' _' + ColumnName + ' As ' + ColumnType + '
				Public Property ' + ColumnName + ' As ' + ColumnType + '
					Get
						' + ColumnName + ' = _' + ColumnName + '
					End Get
					SET(value As INTeger)
						_' + ColumnName + ' = value
					End SET
				End Property
			'
			from
			(
				SELECT 
					replace(col.name, ' ', '_') ColumnName,
					column_id ColumnId,
					case typ.name 
						when 'bigINT' then 'long' 
						when 'binary' then 'byte[]' 
						when 'bit' then 'boolean' 
						when 'char' then 'string' 
						when 'date' then 'DateTime' 
						when 'datetime' then 'DateTime' 
						when 'datetime2' then 'DateTime' 
						when 'datetimeoffSET' then 'DateTimeOffSET' 
						when 'decimal' then 'decimal' 
						when 'float' then 'float' 
						when 'image' then 'byte[]' 
						when 'INT' then 'INTeger' 
						when 'money' then 'decimal' 
						when 'nchar' then 'char' 
						when 'ntext' then 'string' 
						when 'numeric' then 'decimal' 
						when 'nVARCHAR' then 'string' 
						when 'real' then 'double' 
						when 'smalldatetime' then 'DateTime' 
						when 'smallINT' then 'short' 
						when 'smallmoney' then 'decimal' 
						when 'text' then 'string' 
						when 'time' then 'TimeSpan' 
						when 'timestamp' then 'DateTime' 
						when 'tinyINT' then 'byte' 
						when 'uniqueidentIFier' then 'Guid' 
						when 'varbinary' then 'byte[]' 
						when 'VARCHAR' then 'string' 
					end ColumnType
				from sys.columns col
					join sys.types typ on
						col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
				where object_id = object_id(@Name)
			) t
			order by ColumnId

			DECLARE @ResultSubNew VARCHAR(max) =  'Public Sub New() 
			'

			SELECT @ResultSubNew = @ResultSubNew + ' _' + subColumnName + ' = ' + subColumnType + '
			'
			from
			(
				SELECT 
					replace(col.name, ' ', '_') subColumnName,
					column_id subColumnId,
					case typ.name 
						when 'bigINT' then '0' 
						when 'binary' then 'Nothing' 
						when 'bit' then 'False' 
						when 'char' then 'Nothing' 
						when 'date' then 'Today' 
						when 'datetime' then 'Today' 
						when 'datetime2' then 'Today' 
						when 'datetimeoffSET' then '0' 
						when 'decimal' then '0' 
						when 'float' then '0' 
						when 'image' then 'Nothing' 
						when 'INT' then '0' 
						when 'money' then '0' 
						when 'nchar' then 'Nothing' 
						when 'ntext' then 'Nothing' 
						when 'numeric' then '0' 
						when 'nVARCHAR' then 'Nothing'  
						when 'real' then '0' 
						when 'smalldatetime' then 'Today' 
						when 'smallINT' then '0' 
						when 'smallmoney' then '0' 
						when 'text' then 'Nothing'  
						when 'time' then 'Today.Now' 
						when 'timestamp' then 'Today.Now' 
						when 'tinyINT' then '0' 
						when 'uniqueidentIFier' then 'Nothing' 
						when 'varbinary' then 'Nothing' 
						when 'VARCHAR' then 'Nothing'
					end subColumnType
				from sys.columns col
					join sys.types typ on
						col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
				where object_id = object_id(@Name)
			) tSub
			order by subColumnId

			SET @ResultSubNew = @ResultSubNew  + 'End Sub'

			SET @Result = @Result  + '
			' + @ResultSubNew + '
			End Class'
			DECLARE @FileName VARCHAR(300)
			SET @FileName = @Name + '.vb'
			EXEC spWriteToFile @Result, 'C:\SQLClasses', @FileName 
			--Select @Result, @FileName
FetchNextTable:
	FETCH NEXT FROM TableCursor INTO @Name
	
END
CLOSE TableCursor
DEALLOCATE TableCursor