Adding Columns to all Tables

The following T-SQL script loops through all schemas and adds four record audit fields to all database tables:

 DECLARE  @schema nvarchar(255)
   ,@command NVARCHAR(2000)
 DECLARE crs CURSOR FOR
  SELECT name + '.INFORMATION_SCHEMA'
 FROM sys.Databases
 WHERE name NOT IN ('master','tempdb','model','msdb'
  ,'ReportServer','ReportServerTempDB','Sharepoint_ReportServer'
  ,'Sharepoint_ReportServerTempDB','BIDocumenter')
  
 OPEN crsFETCH NEXT FROM crs
 INTO @schema
 
 SET @Command = N'DECLARE @TempTable TABLE (tblName nvarchar(255));
 
 DECLARE @cmd NVARCHAR(2000);
 DECLARE @tblName nvarchar(255);

 INSERT INTO @TempTable
 SELECT TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.['' + TABLE_NAME + '']''
 FROM ' + @schema + '.TABLES
 WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME NOT IN (''sysdiagrams'',''dtproperties'')
 ORDER BY TABLE_NAME;

 DECLARE c CURSOR FOR SELECT * FROM @TempTable;OPEN c;
 FETCH NEXT FROM c INTO @tblName;

 WHILE @@FETCH_STATUS = 0 BEGIN
  SET @cmd = ''ALTER TABLE '''+ ' + @tblName + '' ADD Created_By NVARCHAR(255) NULL DEFAULT (user_name());'';
  PRINT @cmd;
  EXEC sp_executesql @cmd;
  SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD Date_Created DATETIME NULL DEFAULT (GETDATE());'';
  PRINT @cmd;
  EXEC sp_executesql @cmd;
  SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD Modified_By NVARCHAR(255) NULL;'';
  PRINT @cmd;
  EXEC sp_executesql @cmd;
  SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD Date_Modified DATETIME NULL;'';
  PRINT @cmd;
  EXEC sp_executesql @cmd;
  FETCH NEXT FROM c INTO @tblName;
 END
 CLOSE c;
 DEALLOCATE c;'
 
 WHILE @@FETCH_STATUS = 0 BEGIN
  PRINT @Command
  PRINT ''
  
  EXEC sp_executesql @Command
  
  PRINT ''
  
  FETCH NEXT FROM crs
  INTO @schema
  SET @Command = N'DECLARE @TempTable TABLE (tblName nvarchar(255));
  
  DECLARE @cmd NVARCHAR(2000);
  DECLARE @tblName nvarchar(255);

  INSERT INTO @TempTable    
  SELECT TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.['' + TABLE_NAME + '']''
  FROM ' + @schema + '.TABLES
  WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME NOT IN (''sysdiagrams'',''dtproperties'')
  ORDER BY TABLE_NAME;

  DECLARE c CURSOR FOR SELECT * FROM @TempTable;OPEN c;
  FETCH NEXT FROM c INTO @tblName;

  WHILE @@FETCH_STATUS = 0 BEGIN
   SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD CreatedBy NVARCHAR(255) NULL (user_name());'';
   PRINT @cmd;
   EXEC sp_executesql @cmd;
   SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD CreatedBDate DATETIME NULL DEFAULT (GETDATE());'';
   PRINT @cmd;
   EXEC sp_executesql @cmd;
   SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD ModifieddBy NVARCHAR(255) NULL;'';
   PRINT @cmd;
   EXEC sp_executesql @cmd;
   SET @cmd = ''ALTER TABLE ''' + ' + @tblName + '' ADD ModifiedDate DATETIME NULL;'';
   PRINT @cmd;
   EXEC sp_executesql @cmd;
   FETCH NEXT FROM c INTO @tblName;
  END

  CLOSE c;
  DEALLOCATE c;'
 END
 CLOSE crs;
 DEALLOCATE crs;

If you find this script useful please donate generously.