alter 10 tables at the same time

Hi,
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
Jason
Jason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
.....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>
|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>
|||Uri posted example code that you have to modify to suit your needs. The code is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
>