Add a field

I need to add a rowguid to 300+ tables. Is there a way to script it?
Thanks
JeffALTER TABLE ADD ....
There is no built in automated way that I know of but you can use a cursor
to ge thte list of table names and build the alter statement dynamically and
print it out with a GO in between. Just curious as to why you want to add
one to all 300 tables?
Andrew J. Kelly SQL MVP
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:ui31cHiEGHA.648@.TK2MSFTNGP14.phx.gbl...
>I need to add a rowguid to 300+ tables. Is there a way to script it?
> Thanks
> Jeff
>|||Read this: http://www.databasejournal.com/feat...cle.php/1490661
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:ui31cHiEGHA.648@.TK2MSFTNGP14.phx.gbl...
>I need to add a rowguid to 300+ tables. Is there a way to script it?
> Thanks
> Jeff
>|||loop thru information_schema.tables
select @.TableName = table_name from information_schema.tables
use dynamic sql
alter table @.TableName add rowguid int identity
etc etc
http://sqlservercode.blogspot.com/|||"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:ui31cHiEGHA.648@.TK2MSFTNGP14.phx.gbl...
>I need to add a rowguid to 300+ tables. Is there a way to script it?
> Thanks
> Jeff
To add to Andrew's answer, there is an undocumented stored procedure called
sp_MSforeachtable that you could use.
Google it for more information.
Here's one link:
http://www.databasejournal.com/feat...cle.php/1490661|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23s5n4MiEGHA.3896@.TK2MSFTNGP10.phx.gbl...
> Read this:
> http://www.databasejournal.com/feat...cle.php/1490661
Gee Adam, the same link that I gave in my answer.
It's the first one that Google returned, right. ;-)|||I am a pretty new user, so maybe you guys will know a better way than what I
am thinking. I need to replicate two databases. The databases are exactly
the same on the two servers. When I run the wizard to set it up, it asks if
it should copy the schema and data or if the other database has a copy.
Since I already have the database on both servers, the wizard does not
update the schema of either server and add the rowguid to all of the tables.
Since this is the case, I thought I needed to add the field to all of the
tables. There are 300+ tables on each server to set up. I am hoping that
there is a way to add a rowguid to them all through a scipt.
I don't have a lot of experience writing SQL. I understand enough to be
dangerous, but lack the experience to be good. Any hand holding you guys
can give would be greatly appreciated.
Thanks
Jeff
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136484776.370433.268780@.f14g2000cwb.googlegroups.com...
> loop thru information_schema.tables
> select @.TableName = table_name from information_schema.tables
> use dynamic sql
> alter table @.TableName add rowguid int identity
> etc etc
> http://sqlservercode.blogspot.com/
>|||Yes :)
But I usually link to this one, which I think is better:
http://www.databasejournal.com/feat...cle.php/3441031
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:OEZYiUiEGHA.1088@.TK2MSFTNGP10.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23s5n4MiEGHA.3896@.TK2MSFTNGP10.phx.gbl...
> Gee Adam, the same link that I gave in my answer.
> It's the first one that Google returned, right. ;-)
>|||Just my 0,02$, but I would prefer catching the ouput and executing this
in bacthes rather than doing this operation on the tables
automatically. Sure it works with cursor and with the undocumented
procedure, but for me It would be preferable to do this:
select 'ALTER TABLE ' + Table_name + ' ADD YOURCOLUMNNAME DATATYPE'
from information_schema.tables
WHERE TABLE_TYPE = 'BASE_TABLE'
AND ObjectProperty(OBJECT_ID(Table_name), N'IsMSShipped') = 0
HTH, Jens Suessmeyer.|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23SMfiYiEGHA.140@.TK2MSFTNGP12.phx.gbl...
> Yes :)
> But I usually link to this one, which I think is better:
> http://www.databasejournal.com/feat...cle.php/3441031
Yup, much better... but harder on the eyes.
Had to change the Text Size in my browser to at least "Larger" to read it.
I'm getting older, these eyes aren't as good as they used to be.
Mind you, a lot of things aren't as good as they used to be. :-(