Question Locked

How to add SQL Coloumn to over 3k tables

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Running visual studio 2005, need to add a date column to over 3000 tables in an SQL db. How can i go about doing this programmatically instead of manually? I know there is a script to do something like this, but I'm not entirely sure how or where to run it at. HELP!!!! Thank you!

5 replies

by tony hopkinson

But all you need is a cursor to select the tables
and the build an alter table satemnent and exec it.
Some thing like

Declare @TableName VarChar(255)
Declare @QueryString VarChar(255)
Declare TableNameCursor Cursor For Select Name From SysObjects Where Type = 'U'
Open TableNameCursor
Fetch Next From TableNameCursor Into @TableName
While @@Fetch_Status = 0
Begin
Select @QueryString = 'Alter Table ' + @TableName + 'Add Column SomeDate DateTime null'
Exec(@QueryString)
Fetch Next From TableNameCursor Into @TableName
End
Close TableNameCursor
DeAllocate TableNameCursor

Should do the job.
Note this will do all the tables in a database, doesn't check to see if it's already been done, etc.

Might want to back up the db before you start playing with it.. :p

by brian

First, thank you for the response! Would this be ran through a query on the db in visual studios or does it require SQL management studio to do this? And yes, definatly going to backup the tables first.

by tony hopkinson

Management studio would be easier, you'd have to write some code to run it otherwise.

A little tip while you are working on it.
Change
Exec(@QueryString)
To
Print @QueryString

then you'll see each command it would run with exec.

oh and if you've done some of them
you acn join SysObjects to Syscolumns and find all teh tables that already have a SomeDate Column, or with an outer join and test for null, that don't...

Worth spending a bit of time twiddling with this anyway, the techniques (cursors, exec() come in real handy from time to time.

by cmiller5400

It could be done in Enterprise Manager, Query Analyzer, or osql. But it needs to be run on the database, not in Visual Studio.

This discussion is closed to new replies.