UsTMFBdT

1IF OBJECT_ID('[dbo].[SafeAddColumn]') IS NOT NULL
2BEGIN
3  PRINT N'Dropping [dbo].[SafeAddColumn]';
4  DROP PROCEDURE [dbo].[SafeAddColumn];
5END
6GO
7CREATE PROCEDURE [dbo].[SafeAddColumn]
8  @TableName nvarchar(200),
9  @ColumnName nvarchar(200),
10  @Type nvarchar(200),
11  @DefaultValue nvarchar(200),
12  @Nullable bit
13AS
14BEGIN
15  DECLARE @sql nvarchar(max);
16  DECLARE @nullExpr nvarchar(8);
17  IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_SCHEMA]='dbo' AND [TABLE_NAME]=@TableName AND [COLUMN_NAME]=@ColumnName)
18  BEGIN
19    SET @sql = N'ALTER TABLE [dbo].[' + @TableName + N'] ';
20    SET @sql = @sql + N'ADD [' + @ColumnName + N'] ' + @Type + N' ';
21    IF (@DefaultValue IS NOT NULL)
22    BEGIN
23      SET @sql = @sql + N'CONSTRAINT [DF_' + @TableName + N'_' + @ColumnName + N'] DEFAULT ' + @DefaultValue + N' ';
24    END
25    IF (@Nullable = 1)
26    BEGIN
27      SET @sql = @sql + N'NULL';
28    END
29    ELSE
30    BEGIN
31      SET @sql = @sql + N'NOT NULL';
32    END
33    PRINT N'Adding column ''' + @ColumnName + N''' to ' + @TableName;
34    EXEC (@sql);
35  END
36END
37GO