>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 >>
>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 >>
>EXEC CheckTableField 'MyTable', 'MyField', 'varchar', 200 >EXEC CheckTableField 'MyTable', 'MyField', 'int' >EXEC CheckTableField 'MyTable', 'MyField', 'datetime' >>