copy table and global change

[Eradic8or]

I have a SQL database that uses over 50 tables.
In a majority of these tables, the fields are identical. Only the information in the tables is different (these are required for different sites)
Anyway, can someone tell me how to do 2 things.

1). I need to copy a copy and paste a table into the same database just giving it a different name. How do this via enterprise manager and...
2). If I have 35 tables all the same and I want to add/delete a field (not a record) from them all Simultaneously. How would I accomplish this?

Thanks in advance,
Eradic8or.

 

[jhall156]

for #1 you can try this:
In enterprise manager, right click your table and select generate SQL Script
on the next window click preview
on the preview window highlight everythin after and including CREATE TABLE
type <Ctl>c
open up query analyzer for your database
type <Ctl>v
change the table name after the CREATE TABLE
type <f5>
change the name again etc.

I don't know of a way to accomplish #2 with the simultaneous stricture. Terry or the FlutePlr probably have sumpin' for ya. JHall

 

[Eradic8or]

Thanks for the reply. I am bumping this again as I would like a reply to question 2 as well.

Eradic8or.

 

[clapag22]

I am adding a field called 'midname' to all tables that's a varchar(5):

select 'alter table ' + name + ' add midname varchar(5)
GO'
from sysobjects
where type = 'u'

Then take the results, paste them into QA and run it...
Just be careful as some non-user tables can be marked as user tables (dtproperties comes to mind)...

 

[Eradic8or]

Ok, thx for that. What is the &quot;Where type = 'u'&quot;. What does this statement mean?

Eradic8or.