|
Home > Archive > MS SQL Server > October 2006 > alter 10 tables at the same time
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
alter 10 tables at the same time
|
|
| Jason Huang 2006-10-24, 6:31 pm |
| 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 Dimant 2006-10-24, 6:32 pm |
| 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_SCHE
MA) +
N'.' +
QUOTENAME(TABLE_NAME
)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJEC
T_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME
)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" < JasonHuang8888@hotma
il.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
---
| |
| Jason Huang 2006-10-24, 6:33 pm |
| Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@iscar.co.il> 撰寫於郵件新聞:OMnZO
Wh4GHA.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_SCHE
MA) +
> N'.' +
> QUOTENAME(TABLE_NAME
)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJEC
T_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME
)), 'IsMSShipped') = 0
>
>
> GO
>
>
> DROP TABLE T1,T2,T3
>
> "Jason Huang" < JasonHuang8888@hotma
il.com> wrote in message
> news:%23471BFh4GHA.2208@TK2MSFTNGP04.phx.gbl...
>
>
| |
| Jason Huang 2006-10-24, 6:33 pm |
| Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@iscar.co.il> 撰寫於郵件新聞:OMnZO
Wh4GHA.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_SCHE
MA) +
> N'.' +
> QUOTENAME(TABLE_NAME
)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJEC
T_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME
)), 'IsMSShipped') = 0
>
>
> GO
>
>
> DROP TABLE T1,T2,T3
>
> "Jason Huang" < JasonHuang8888@hotma
il.com> wrote in message
> news:%23471BFh4GHA.2208@TK2MSFTNGP04.phx.gbl...
>
>
| |
| Tibor Karaszi 2006-10-24, 6:33 pm |
| 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@hotma
il.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> 撰寫於郵件新聞:OMnZO
Wh4GHA.3600@TK2MSFTNGP03.phx.gbl...
>
>
|
|
|
|
|