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.
JasonJason
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> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> 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
>|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> 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
>|||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> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
>> 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
>>
>