Adding the same columns to every existing table in a SQL database

I have been asked to add the same 2 columns (CUID and LUID) to every table in a database and script these changes. I am currently modifying each table one by one and copying the resulting change script into the final upgrade script. However, since there are 253 tables it will take a while to do every one! So, I am wondering if there is a faster/simpler way to do this - I am wondering if there is some kind of FOREACH statement I could use which would call the SQL ALTER TABLE function for each.
jpguillebaudAsked:
Who is Participating?
 
cmhuntyConnect With a Mentor Commented:
Think this'll work:


DECLARE @tableID char(11)
DECLARE @tableName char(255)
DECLARE @strSQL nvarchar(4000)
DECLARE @tableCount int

SET @tableCount = 0

-- Declare cursors
DECLARE cTable CURSOR READ_ONLY
FOR
SELECT id, [name] FROM sysobjects WHERE xtype = 'U'
---------------------------------------------
OPEN cTable

FETCH NEXT FROM cTable INTO @tableID, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
      SET @tableCount = @tableCount + 1
      
      SET @strSQL = 'ALTER TABLE ' + CAST(@tableName AS varchar) + ' ADD CUID int'
      EXEC(@strSQL)

      FETCH NEXT FROM cTable INTO @tableID, @tableName

      
END

CLOSE cTable
DEALLOCATE cTable

PRINT 'Tables amended: ' + CAST(@tableCount AS nvarchar)
0
 
cmhuntyCommented:
You can get the table names by:
SELECT name FROM sysobjects WHERE xtype = 'U'

You could then put this into a cursor and at each step, create a dynamic SQL alter table statement using the table name and execute.

Chris
0
 
jpguillebaudAuthor Commented:
Thanks Chris,
Your script works perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.