SQL Server – Altering Multiple Tables

alter-tablesql serversql-server-2012

I have database with 170+ tables in mssql 2012. Some of them contain the field "UserID", varchar(9) while others do not. Due to an application redesign, I need to alter all tables in the database to check if the field exists, and if it does, I need to change it to varchar(50). If not, then I need to add it.

Can someone point me how to do this with some kind of batch procedure? I don't have much experience with mssql databases, so detailed explanation would be appreciated.

Best Answer

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 < 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!
Related Question