>>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 :-)

Wow. Thanks. I'll let you know how it went as I get to it.

Alex