Adding a new column in 320 table

Question

Post reply

Adding a new column in 320 table

karthik M

One Orange Chip

Points: 29139
More actions
December 4, 2008 at 7:00 am

#196861

Hi All,

I have to add a new column in all the tables which resides in a database.

say for example,

DB Name : Employee

Table Count : 320

Now, I have to add a column called dt_log ( Datatype : Datetime) in all the tables. i.e 320 tables.

will 'DYNAMIC SQL' concept help to do this?

#2:

if the table has index ( clustered or non clustered whatever it may be), the new column should be added in that key.

say for example,

Table Name: emp

Columns:

eno int

ename varchar(25)

dob datetime

age int

salary numeric(8,2)

index name : id01 on (eno,ename,dob)

then dt_log should be modified as below.

i.e

index name : id01 on (eno,ename,dob,dt_log)

how ?

Inputs are welcome!

karthik

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

As far as #1 goes, a cursor with the names of all your tables and some dynamic sql should handle it.

For the second part... that sounds dangerous and arbitrary and I don't think I'd do it.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jerry Hung SSChampion Points: 12908 More actions · Answer 2

You could utilize the sp_MSForEachDB if you don't want cursor

but same idea.



sp_MSForEachDB 'ALTER TABLE ? ADD ColumnName ColumnType'

As for second question, it does sound dangerous

but my thinking is cursor for all indexes, and add the column into the index

SQLServerNewbie MCITP: Database Administrator SQL Server 2005

Garadin One Orange Chip Points: 29613 More actions · Answer 3

I think he'd want: sp_MSForEachTable (And it uses a cursor anyways, but it does save some typing and probably looks a bit cleaner overall)

And with the indexes, you could potentially add it to multiple indexes per table with that approach.

Karthik,

Why does this field need to be added indiscriminately to all these indexes?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Why does this field need to be added indiscriminately to all these indexes?

This new field should be used in the where clause as a filter.

Say for example,

TableName: Emp

Columns:

Eno int

DOB Datatime

Ename varchar(25)

Salary

Index : id01 on (eno,DOB)

Now,

the new column dt_log has been added.

if i write query like

select eno,DOB,Salary

from emp

where dt_log > '01/01/2008'

it wont use the above index (id01). Am i right ?

Thats why i want to include dt_log column in the existing index.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

karthik M

One Orange Chip

Points: 29139

December 8, 2008 at 2:11 am

#909029

Any inputs ?

karthik

dongadoy SSC Eights! Points: 870 More actions · Answer 6

dongadoy

SSC Eights!

Points: 870

December 9, 2008 at 9:20 am

#909737

select eno,DOB,Salary

from emp

where dt_log > '01/01/2008'

The query will not make use of an index on (eno, DOB, dt_log). Try it.

Perhaps you want to add to every table a new index with dt_log as the only column?