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 (....)] ENDYou 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) ENDand 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 :-)