Tuesday, August 13, 2013

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
Post a Comment