>I need a routine to update the structure of a SQL Server table from a VFP app.
>
>The changes I need to make are relatively simple, probably just FieldName, FieldType, Default value. Fields may be added, dropped or modified, including posibly casting to a different type.
>
>Any suggestions or gotchas?
>
>TIA,
>
>Alex

IF NOT EXSIST(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE Table_Name = YourTable AND Column_Name = YourColumn)
   BEGIN
       --- Column do not exsit, just add it
      ALTER TABLE YourTable ADD YourColumn DesiredType (length [,precision]) [NULL | NOT NULL] [DEFAULT (....)]
      --- Check ALTER TABLE in BOL for full syntax
   END
ELSE
   --- Column exist
   BEGIN
      DECLARE @FieldType      as varchar(200)
      DECLARE @FieldLength    as int
      DECLARE @FieldPrecision as int
--- If you need add more variables here to get all info you need from INFORMATION_SCHEMA.Columns
      SELECT @FieldType      = Data_Type,
             @FieldLength    = COALESCE(Character_Maximum_Length, Numeric_Precision,....),
             @FieldPrecision = ISNULL(Numeric_Scale, 0)
             FROM INFORMATION_SCHEMA.Columns
      WHERE Table_Name = YourTable AND Column_Name = YourColumn
      IF @FieldType      <> DesiredFieldType   OR
         @FieldLength    <> DesiredFieldLength OR
         @FieldPrecision <> DesiredFieldPrecision
         ALTER TABLE YourTable ALTER COLUMN YourColumn DesiredType (length [,precision]) [NULL | NOT NULL] [DEFAULT (....)]
   END

You could create a SP as following

CREATE PROCEDURE CheckTableField(
       @YourTable varchar(200),
       @YourField varchar(200),
       @YourFieldType varchar(200),
       @YourFieldLength    int = 0,
       @YourFieldPrecision int = 0)
AS
    BEGIN
         DECLARE @SQL as varchar(8000)
         IF NOT EXSIST(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE Table_Name = @YourTable AND Column_Name = @YourField)
            BEGIN
                SET @SQL = 'ALTER TABLE '+@YourTable+' ADD '+@YourField+' '+ @YourFieldType +
                           CASE WHEN @YourFieldLength <> 0
                                THEN ' ('+CAST(@YourFieldLength as varchar(200))+')'
                           ELSE '' END+
                           CASE WHEN @YourFieldPrecision <> 0 AND @YourFieldLength <> 0
                                THEN ', '+CAST(@YourFieldPrecision as varchar(200))
                                ELSE '' END + ') [NULL | NOT NULL] [DEFAULT (....)]'
            END
         ELSE
            BEGIN
               DECLARE @FieldType      as varchar(200)
               DECLARE @FieldLength    as int
               DECLARE @FieldPrecision as int
               SELECT @FieldType      = Data_Type,
                      @FieldLength    = COALESCE(Character_Maximum_Length, Numeric_Precision,....),
                      @FieldPrecision = ISNULL(Numeric_Scale, 0)
                      FROM INFORMATION_SCHEMA.Columns
               WHERE Table_Name = YourTable AND Column_Name = YourColumn
               IF @FieldType      <> DesiredFieldType   OR
                  @FieldLength    <> DesiredFieldLength OR
                  @FieldPrecision <> DesiredFieldPrecision
                  SET @SQL = 'ALTER TABLE '+@YourTable+' ALTER COLUMN '+@YourField+' '+ @YourFieldType
                              CASE WHEN @YourFieldLength <> 0
                                   THEN ' ('+CAST(@YourFieldLength as varchar(200))+')'
                              ELSE '' END+
                              CASE WHEN @YourFieldPrecision <> 0 AND @YourFieldLength <> 0
                                   THEN ', '+CAST(@YourFieldPrecision as varchar(200))
                                   ELSE '' END + ') [NULL | NOT NULL] [DEFAULT (....)]'
            END
      IF @SQL IS NOT NULL
         EXEC (@SQL)
    END

and you could call this SP from anywhere:

EXEC CheckTableField 'MyTable', 'MyField', 'varchar', 200
EXEC CheckTableField 'MyTable', 'MyField', 'int'
EXEC CheckTableField 'MyTable', 'MyField', 'datetime'

THAT IS NOT TESTED. I wrote this directly here :-)