[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-19
3
Medium Priority
?
177 Views
Last Modified: 2010-03-19
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.
0
Comment
Question by:jpguillebaud
  • 2
3 Comments
 
LVL 11

Expert Comment

by:cmhunty
ID: 20107363
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
 
LVL 11

Accepted Solution

by:
cmhunty earned 1000 total points
ID: 20107374
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
 

Author Comment

by:jpguillebaud
ID: 20107464
Thanks Chris,
Your script works perfectly!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question