How to add a column to all tables which don’t have it already in SQL Server

In SQL Server if you need to add a column to all tables which don’t have it already I found the below script on Stack Exchange the other day which helps create the ALTER statements needed…

SELECT 'ALTER TABLE ' + QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) + ' ADD created_date DATETIME NULL;'
FROM sys.tables st
INNER JOIN sys.schemas ss on st.[schema_id] = ss.[schema_id]
WHERE st.is_ms_shipped = 0
AND NOT EXISTS (
 SELECT 1
 FROM sys.columns sc
 WHERE sc.[object_id] = st.[object_id]
 AND sc.name = 'created_date'
)

The example below shows that the created_date column is not in three tables. The generated ALTER statements can be easily reviewed before executing them.

We could also easily modify lines 4 and 8 to create drop statements for a column for all tables in which it exists.