/*
params:
@dbType – holds the data type defined in the table structure
@fldName – field/column name
@len – storage length of the column if data type is varchar/char etc.
@precision – numeric
@scale – numeric
@newType – equivalent data type in vb.net; ex. varchar -> string
@struct – script
@tablename – table name
*/
DECLARE @dbType VARCHAR(50)
DECLARE @fldName VARCHAR(50)
DECLARe @len smallint
DECLARE @precision smallint
DECLARE @scale smallint
DECLARE @newType VARCHAR(20)
DECLARE @struct VARCHAR(MAX)
DECLARE @tablename VARCHAR(20)
SET @tablename = ‘tblExchangeDocument’
SET @newType = ”
SET @struct = ‘Public Class ‘ + RIGHT(@tablename, LEN(@tablename) – 3)
DECLARE rs CURSOR FOR
SELECT sys.columns.[name] as fldname, sys.types.name as dbtype, sys.columns.[max_length], sys.columns.[precision], sys.columns.[scale]
FROM sys.columns INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
WHERE object_id = OBJECT_ID(@tablename)
OPEN rs
FETCH NEXT FROM rs
INTO @fldname, @dbType, @len, @precision, @scale
WHILE @@FETCH_STATUS = 0
BEGIN
IF @dbType = ‘varchar’
SET @newType = ‘String’
IF @dbType = ‘bigint’
SET @newType = ‘long’
IF @dbType = ‘int’
SET @newType = ‘Integer’
IF @dbType = ‘datetime’
SET @newType = ‘date’
SET @struct = @struct + ”
SET @struct = @struct + char(13) + char(9) + ‘ Public ‘ + @fldname + ‘ AS ‘ + @newType
FETCH NEXT FROM rs
INTO @fldName, @dbType, @len, @precision, @scale
END
CLOSE rs
DEALLOCATE rs
SET @struct = @struct + char(13) + ‘ End Class’
print @Struct