Question

Locked

How to add SQL Coloumn to over 3k tables

By brian ·
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!

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

No idea where the script is but

by Tony Hopkinson In reply to How to add SQL Coloumn to ...

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..

Collapse -

Reponse To Answer

by brian In reply to No idea where the script ...

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.

Collapse -

Reponse To Answer

by Tony Hopkinson In reply to No idea where the script ...

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.

Collapse -

It would be done in...

by cmiller5400 In reply to How to add SQL Coloumn to ...

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

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums