Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you sure you want such a field in every table? I smell wrong design here... –  Lohoris Apr 1 at 8:27
    
Yes i need that field because i need to know owner of every single record in every single table in my db... That's customer requirement... –  onedevteam.com Apr 1 at 9:00
    
Exactly, you're doing it wrong. You should use a numeric id instead, and have another table which couples that numeric id with the varchar you like (I'm very surprised nobody pointed it out, wtf?!?). –  Lohoris Apr 1 at 10:35

2 Answers 2

up vote 7 down vote accepted
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!
share|improve this answer
2  
+1 Completely missed the part to add the column if it doesn't exist. This answer is the complete and correct one. –  Thomas Stringer Mar 31 at 13:54

Here is a query that does a few things:

use YourDatabase;
go

select
    object_name = 
        object_name(c.object_id),
    column_name = c.name,
    type_name = t.name,
    c.max_length,
    alter_table_ddl = 
        'alter table ' + 
        quotename(object_schema_name(c.object_id)) + '.' + 
        quotename(object_name(c.object_id)) + 
        ' alter column ' + quotename(c.name) + 
        ' varchar(50);'
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
where c.name = 'UserID'
--and c.max_length = 9;

You can uncomment that last line (the AND clause for the filtering) to just pull the columns in your database that are varchar(9).

So this query will identify the columns that you are concerned with, as well as the last column for the query's result set will be the ALTER TABLE ... ALTER COLUMN DDL that should be the T-SQL text to make the column alteration.

It sounds like this may be a non-production environment/database, but it is worth saying that this operation could be extremely invasive to actually make all these changes. Either way, make sure end-users will have minimal impact, and also make sure you have a valid backup of this database before you do it. Likewise, review the output of the query. Never just take output, especially DDL-generating output, and blindly execute it.

EDIT: I completely missed the portion of your question where your requirement is also to add the column if it doesn't exist. Apologies there, and I won't redo Aaron's work, as it looks like he already covered that requirement.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.