DB Property Create --- By Rob McCrady

If you want to quickly generate a property list for a VB class from the list of columns on a DB table, this script is a way to do that quickly without a lot of typing.

Just set the value of @tablename to the name of the table you’re scripting and run the script.  Copy and Paste the resultset directly into yoru .vb file.

Note:  In this script I’m converting only varchar, int, bit, and the datetime db types into vb types.  If your table has other db types, you’ll want to add those to the CASE statement.

declare @tableName varchar(50)

set @tableName = 'tblSlotMgmt'

select

 'Public Property ' + propName + ' as ' + type

from (

select c.name as propName

, t.name

, Case t.name

      WHEN 'int' then 'integer'

      WHEN 'varchar' then 'string'

      WHEN 'datetime' then 'datetime'

      when 'smalldatetime' then 'datetime'

      when 'bit' then 'boolean'    

      else ''

  end as type

, ColOrder

FROM sysobjects o

inner join syscolumns c on c.id = o.id

inner join systypes t on c.xtype = t.xtype

where o.name = @tableName

and t.uid = 4

) d

order by ColOrder