====== Add a column to a table ======

This temporary SP adds a column to a table, with a couple of extra features:
  * if the column already exists, the SP returns without action or error;
  * the column can be created NOT NULL be specifying an initial value. This is specified as a default value to add and populate the column, after which the constraint is automatically dropped.

<code tsql>IF OBJECT_ID('tempdb..#spAddColumn') IS NOT NULL
  DROP PROCEDURE #spAddColumn

GO

CREATE PROCEDURE #spAddColumn (
  @SchemaName NVARCHAR(128)
, @TableName NVARCHAR(128)
, @ColumnName NVARCHAR(128)
, @DataType NVARCHAR(50)
, @InitialValue NVARCHAR(50) = NULL
)
AS
BEGIN
 
  IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = @SchemaName
    AND TABLE_NAME = @TableName
    AND COLUMN_NAME = @ColumnName
  )
  RETURN  -- nothing to do
 
  -- add the non-null column with a default value
  DECLARE @sql NVARCHAR(MAX) = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + ']
ADD [' + @ColumnName + '] ' + @DataType + ' '
+ COALESCE('NOT NULL DEFAULT ''' + @InitialValue + '''', 'NULL')
 
  PRINT @sql
  EXEC(@sql)
  SET @sql = NULL
 
  -- remove the default constraint
  SELECT
    @sql = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + ']
DROP CONSTRAINT [' + dc.name + ']'
  FROM sys.schemas s
    INNER JOIN sys.tables t ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON c.object_id = t.object_id
    INNER JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
  WHERE s.name = @SchemaName
  AND t.name = @TableName
  AND c.name = @ColumnName
 
  PRINT @sql
  EXEC(@sql)

END
</code>

{{tag> tsql dynamic development}}