Altering Multiple Tables
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += [sql] FROM
(
  SELECT [sql] = N'
  ALTER TABLE '
    + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' ALTER COLUMN ' + c.name + ' VARCHAR(50);'
  FROM sys.columns AS c
  INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE LOWER(c.name) = N'userid'
  AND c.max_length &lt; 50
  UNION ALL 
  SELECT N'
  ALTER TABLE '
    + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' ADD UserID VARCHAR(50);'
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE NOT EXISTS
  (
   SELECT 1 FROM sys.columns 
     WHERE LOWER(name) = N'userid'
     AND [object_id] = t.[object_id]
  )
) AS x;

PRINT @sql;

--EXEC sp_executesql @sql;


If the string is too long to properly validate via PRINT, see this tip for other ideas.

You'll want to also make sure that you update any stored procedures that take this as a varchar(9) parameter, since these will silently truncate, and also any explicit conversions or variable declarations you are doing in ad hoc SQL, views and other modules, etc. For example if you have two users, one with ID = frankenstein and the other just frankenst:

DECLARE @UserID VARCHAR(9) = 'frankenstein';
SELECT @UserID;


Result:

frankenst -- this will match the wrong user!