Check all tables in a schema for X fields? I want to make sure that all of the tables in my schema have fields named "updateddate" and "createddate". Obviously, I could check them manually, but how can I generate a list of tables that are missing these fields? Even better, how can I use the resulting list and generate the SQL needed to create the missing column for that table?
DECLARE @sch SYSNAME = N'my_schema_name';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) + '.' + QUOTENAME(t.name)
  + ' ADD createddate DATETIME
      NOT NULL DEFAULT GETDATE();' 
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = N'createddate'
  );

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) +'.' + QUOTENAME(t.name)
  + ' ADD updateddate DATETIME;' 
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = N'updateddate'
  );

PRINT @sql;
--EXEC sys.sp_executesql @sql;


To automate this a little further, you can use a table variable or #temp table (no such thing as an array in SQL Server):

DECLARE @cols TABLE
(
  col SYSNAME, 
  ctype SYSNAME,
  def SYSNAME
);

INSERT @cols(col,ctype,def) VALUES
(N'createddate', N'DATETIME', N'NOT NULL DEFAULT GETDATE()'),
(N'updateddate', N'DATETIME', N'NULL'),
(N'somethingelse', N'VARCHAR(32)', N'');

DECLARE @sch SYSNAME = N'my_schema_name';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) + '.' + QUOTENAME(t.name)
  + ' ADD ' + QUOTENAME(cols.col) + ' ' + cols.ctype 
  + ' ' + cols.def + ';' 
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
CROSS JOIN @cols AS cols
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = cols.col
  );

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


Note that in both cases above, depending on the number of tables that don't have these columns, PRINT might truncate the output it shows you, but this is due to an inherent 8K limitation of PRINT (or less data depending on your settings in SSMS). I can assure you the command it will execute doesn't end abruptly like that.