#!----------------------------------------------------------------------------------------
#TEMPLATE(SMSSQL,'MS-SQL Templates Schoeffler & Rau Datensysteme'),FAMILY('ABC')
#!----------------------------------------------------------------------------------------
#! Schoeffler & Rau Datensysteme (c) 1999-2006 Germany
#! http://www.schoefferl.biz
#! mailto:info@schoeffler.biz
#! 
#! Version 3
#!
#UTILITY(SaveDICasSQL, 'Save Dictionary as MSQL-Script')
 #DISPLAY('Save Dictionary as SQL-Script')
 #DISPLAY('1999 (c) by Ralf Schoeffler')
 #DISPLAY('')
 #PROMPT('save as: ',@S80),%OutputFile,default(sub(%DictionaryFile,1,len(%DictionaryFile)-4) & '.sql')
 #PROMPT('create all  Tables',Check),%AllTables,default(1)
 #PROMPT('create ralations',Check),%CreateRelation,default(0)
 #PROMPT('drop Table',Check),%DropTable,default(0)
 #BUTTON ('Script only for Tables...'), MULTI(%InclTables, %InclTable)
   #PROMPT ('Table :', FROM(%FILE)), %InclTable, REQ
 #ENDBUTTON
 #DECLARE(%NFieldType)
 #DECLARE(%NKeyName)
 #DECLARE(%NKeyFields)
 #DECLARE(%NKeyField)
 #DECLARE(%NKeyFieldCount)
 #DECLARE(%TableCount)
 #DECLARE(%KeyCount)
 #DECLARE(%FieldCount)
 #DECLARE(%PKeyCount)
 #DECLARE(%PrimaryCount)
 #DECLARE(%NRelateFile)
 #DECLARE(%Relationfield1)
 #DECLARE(%Relationfield2)
 #DECLARE(%CStringLen)
 #DECLARE(%LastField)
 #DECLARE(%InnerGroup)

 #CREATE(%OutputFile)

SET QUOTED_IDENTIFIER ON
 #FOR( %File )
 #IF (%AllTables = 0)
   #FIND (%InclTable, %File)
   #IF (%InclTable = '')
     #CYCLE
   #ENDIF
 #ENDIF
 #IF (SUB(%FileDriver,1,5) = 'MSSQL' )
 #SET(%TableCount, %TableCount + 1)
 #IF(%DropTable)
DROP TABLE %File

 #ENDIF

CREATE TABLE "%FILE"(
   #FOR(%Field)
     #IF(%FieldType = 'STRING' AND %FieldMemoSize = 8)
       #SET(%LastField, %FieldID)
       #CYCLE
     #ENDIF
     #IF(%FieldType='GROUP')
       #SET(%InnerGroup,1)
       #IF(INSTRING('OVER(',%FieldStatement,1,1))
     "%LastField"  DATETIME,
         #SET(%FieldCount, %FieldCount + 1)
       #ENDIF
       #CYCLE
     #ENDIF
     #IF(%FieldType='END')
       #SET(%InnerGroup,0)
       #CYCLE
     #ENDIF
     #IF(%InnerGroup = 1) 
       #CYCLE
     #ENDIF
     #SET(%FieldCount, %FieldCount + 1)
     #CASE(%FieldType)
       #OF('ULONG')   #SET(%NFieldType,'INT')
       #OF('LONG')    #SET(%NFieldType,'INT')
       #OF('SHORT')   #SET(%NFieldType,'SMALLINT')
       #OF('REAL')    #SET(%NFieldType,'FLOAT')
       #OF('STRING')  #SET(%NFieldType,'CHAR(' & %FieldMemoSize & ')' )
       #OF('CSTRING') #SET(%CStringLen,%FieldMemoSize - 1)
                      #SET(%NFieldType,'VARCHAR(' & %CStringLen & ')' )
       #OF('BYTE')    #SET(%NFieldType,'TINYINT')
       #OF('DATE')    #SET(%NFieldType,'DATETIME')
       #OF('DECIMAL') #SET(%NFieldType,'DECIMAL(' & %FieldMemoSize & ',' & %FieldPlaces & ')')
     #ENDCASE
     "%FieldID" %NFieldType,
   #ENDFOR

#! primary key
   #SET(%PrimaryCount,0)
   #FOR(%Key)
     #SET(%NKeyName,%KeyID)
     #SET(%NKeyFields,'')
     #SET(%NKeyFieldCount,0)
     #FOR(%KeyField)
       #SET(%NKeyField, sub(%KeyField, instring(':',%KeyField,1,1) + 1 , 50))
       #SET(%NKeyFieldCount,%NKeyFieldCount + 1 )
       #IF(%NKeyFieldCount = 1)
         #SET(%NKeyFields,'"' & %NKeyField & '"')
       #ELSE
         #SET(%NKeyFields, %NKeyFields & ' ,"' & %NKeyField & '"')
      #ENDIF
     #ENDFOR
     #IF(%KeyPrimary)
     #SET(%PrimaryCount,1)
     #SET(%PKeyCount, %PKeyCount + 1)
     #SET(%KeyCount, %KeyCount + 1)
     CONSTRAINT "%NKeyName" PRIMARY KEY (%NKeyFields))
     #ENDIF
   #ENDFOR
   #IF(%PrimaryCount = 0)
     )
   #ENDIF

#! keys without the primary
   #FOR(%Key)
       #SET(%NKeyName,%KeyID)
       #SET(%NKeyFields,'')
       #SET(%NKeyFieldCount,0)
       #FOR(%KeyField)
         #SET(%NKeyField, sub(%KeyField, instring(':',%KeyField,1,1) + 1 , 50))
         #SET(%NKeyFieldCount,%NKeyFieldCount + 1 )
         #IF(%NKeyFieldCount = 1)
           #SET(%NKeyFields,'"' & %NKeyField & '"')
         #ELSE
           #SET(%NKeyFields, %NKeyFields & ' ,"' & %NKeyField & '"')
         #ENDIF
       #ENDFOR
     #IF(NOT %KeyPrimary)
     #SET(%KeyCount, %KeyCount + 1)
     CREATE INDEX "%NKeyName" ON "%File" (%NKeyFields)
     #ENDIF
   #ENDFOR
 #ENDIF
 #ENDFOR

#! Relations
 #IF(%CreateRelation)
   #FOR( %File )
   #IF (%AllTables = 0)
     #FIND (%InclTable, %File)
     #IF (%InclTable = '')
       #CYCLE
     #ENDIF
   #ENDIF
   #IF (SUB(%FileDriver,1,5) = 'MSSQL' )
    #FOR(%Key)
      #FOR(%Relation),WHERE(%FileKey = %Key)
        #IF(%FileRelationType = 'MANY:1')
          #SET(%NKeyField, sub(%FileKey, instring(':',%FileKey,1,1) + 1 , 50))
          #SET(%NRelateFile, sub(%RelationKey, 1, instring(':',%RelationKey,1,1) -1 ))
          #FOR(%KeyField)
            #FIX(%FileKeyField,%KeyField)
            #IF(%FileKeyField)
              #SET(%Relationfield1,sub(%FileKeyField, instring(':',%FileKeyField,1,1)+1, 50))
              #SET(%Relationfield2,sub(%FileKeyFieldLink, instring(':',%FileKeyFieldLink,1,1)+1, 50))
            #ENDIF
          #ENDFOR
        #ENDIF
        #IF(%Relationfield1)

ALTER TABLE "%File" ADD
   CONSTRAINT "%NKeyField" FOREIGN KEY ("%Relationfield1")
   REFERENCES "%NRelateFile"("%Relationfield2")

          #SET(%Relationfield1,'')
          #SET(%Relationfield2,'')
        #ENDIF
      #ENDFOR
    #ENDFOR
   #ENDIF
   #ENDFOR
 #ENDIF


/* Tables       : %TableCount */
/* Primary Keys : %PKeyCount */
/* Keys         : %KeyCount */
/* Fields       : %FieldCount */
 #CLOSE