Creates a dh file based on the current definition of a table Solomon

/****** Object:  StoredProcedure [dbo].[ut_dhcreate]    Script Date: 05/29/2012 20:13:09 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER proc [dbo].[ut_dhcreate] @tabname varchar(50),
                      @path    varchar(20),
                      @dhname       varchar(36)
as
/********************************************************************************
*             Copyright TLB, Inc. 1991, 1994 All Rights Reserved
*
* Proc Name     : ut_dhcreate
*
* Narrative     : Creates a dh file based on the current definition of a table.
*
* Inputs        : tabname     ie. APDoc
*                 path        This is the destination of the dh file. This is
*                             relative to the sqlserver box, not the client.
*                             For example,  C:\temp\    will put in on the
*                             database servers C drive.
*                 dhname      name of "dh" file to create, usually same as table
*                                 name with ".dh" suffix. (ie. APDoc.dh)
*
********************************************************************************
* #  Init  Date     Change
********************************************************************************
*001  CLS   06/29/98  Initial Creation
*********************************************************************************
*/

declare @command varchar(255),          @temp_colname varchar(32),
        @col_name  char(32),
        @col_type  varchar(65),
        @col_length char(15)

/*** Build Header stuff ***/
/***                    ***/
set @tabname = ltrim(rtrim(@tabname))

select @command='echo Option Explicit> '+@path+@dhname
exec master.dbo.xp_cmdshell @command

select @command='echo. >> '+@path+@dhname
exec master.dbo.xp_cmdshell @command

select @command='echo Attribute VB_Name = "' + @tabname + 'DH">> '+@path+@dhname
exec master.dbo.xp_cmdshell @command

select @command='echo Type ' + @tabname + '>> '+@path+@dhname
exec master.dbo.xp_cmdshell @command

declare col_cursor scroll cursor for
select  sc.name ,
        case
          /* Convert db types to vb types.                    */
          /* All converts are straightforward except date/time         */
          /* which converts as follows.                                */
          /*     If a field name ends in "datetime" ===> sdate         */
          /*     If a field name ends in "time"                        */
          /*                  other than "datetime" ===> stime         */  
          when (t.name = 'char')   then 'As String * '
          when (t.name = 'smallint')       then 'As Integer'
          when (t.name = 'float')          then 'As Double'
          when (t.name = 'Decimal')          then 'As Decimal'
          when (t.name = 'smalldatetime'
            and (sc.name like '%time%'
                   and sc.name not like '%datetime%'))
                                          then 'As Stime'
          when (t.name = 'smalldatetime'
            and (sc.name not like '%time%'
                   or sc.name like '%datetime%'))      
                                          then 'As Sdate'
          when (t.name = 'int')   then 'As Long'
          when (t.name = 'image')          then 'As String * 1900'
          when (t.name = 'text')           then 'As String * 1900'
          else 'Unknown type ' + t.name
        end type,
        case t.name
          when 'char' then substring(ltrim(str(sc.length)),1,datalength(convert(varchar,sc.length)))
          else ''
        end length
        from syscolumns sc,
             systypes   t
       where sc.id=object_id(@tabname)
         and t.usertype = sc.usertype
         and t.name <> 'timestamp'
    order by sc.colid

open col_cursor

fetch col_cursor into
      @col_name,
      @col_type,
      @col_length

while (@@fetch_status = 0)
  begin

   /*** Check for arrays ***/
   /*** Arrays will be identified for now as         ***/
   /*** columns ending with a two position number    ***/
   /*** with a column name other than S4Future       ***/

  if (@col_name not like 'S4Future%' and
     substring(@col_name,datalength(rtrim(@col_name))-1,2) = '00' )
  begin
     select @temp_colname = substring(@col_name,1,datalength(rtrim(@col_name))-2)      
     while (@@fetch_status = 0 and
            substring(@col_name,1,datalength(rtrim(@col_name))-2) = @temp_colname)
     begin
        fetch col_cursor into
          @col_name,
          @col_type,
          @col_length
     end        
     fetch prior from col_cursor into
          @col_name,
          @col_type,
          @col_length
     select @col_name = substring(@col_name,1,datalength(rtrim(@col_name))-2) + '(0 To '+
             substring(substring(@col_name,datalength(rtrim(@col_name))-1,2),
                       patindex('%0%',substring(@col_name,datalength(rtrim(@col_name))-1,1))+1,
                     2 - patindex('%0%',substring(@col_name,datalength(rtrim(@col_name))-1,1))) +
            ')' +Space(30)        

  end
     
  select @command='echo     ' + rtrim(@col_name + @col_type + '' + @col_length)+ ' >> ' + @path+@dhname


exec master.dbo.xp_cmdshell @command
  fetch next from col_cursor into
      @col_name,
      @col_type,
      @col_length      
end
close col_cursor
deallocate col_cursor

/*** print footer stuff ***/

select @command='echo End Type>> '+@path+@dhname
exec master.dbo.xp_cmdshell @command


select @command='echo. >> '+@path+@dhname
exec master.dbo.xp_cmdshell @command

select @command='echo Public b' + @tabname + ' As ' + @tabname + ', n' + @tabname + ' As ' + @tabname + '>> '+@path+@dhname
exec master.dbo.xp_cmdshell @command