Changing all Int to Bigint

Hi,
We need to change all Int data types to Bigint. Considering the number of
tables and View tables, changing manually one by one is not a option. Is
there a nicer way of changing all (hundreds of them) in more automatic way?
YCYou'll have to script this kind of thing. You can start with
INFORMATION_SCHEMA.COLUMNS and filter on DATA_TYPE. You can generate a
bunch of ALTER TABLE statements from that.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"YC" <asppsa@.hotmail.com> wrote in message
news:%23NDqO2DhGHA.4452@.TK2MSFTNGP05.phx.gbl...
Hi,
We need to change all Int data types to Bigint. Considering the number of
tables and View tables, changing manually one by one is not a option. Is
there a nicer way of changing all (hundreds of them) in more automatic way?
YC|||On Tue, 30 May 2006 16:05:30 -0700, YC wrote:

>Hi,
>We need to change all Int data types to Bigint. Considering the number of
>tables and View tables, changing manually one by one is not a option. Is
>there a nicer way of changing all (hundreds of them) in more automatic way?
Hi YC,
Tom told you how to do this. But I just have to ask why you want to do
this. I can imagine that the -2,147,483,648 to 2,147,483,647 range is
insufficient for SOME columns - but all'?
Have you considered the impact that this change will have on your DB's
storage requirements? All int columns will double in size - this will
impact storage requirement for tables with integer columns, but for
indexes on integer columns as well (and any nonclustered indexes if the
clustered index of the same table is on an integer column).
How about performance - more bytes per data row (and per index row)
means less rows per page. Means more logical page reads. But also less
cache hits, and hence more physical page reads.
I recommend you to change just the columns that need the ennhanced range
of bigint. Keep other columns as int.
Hugo Kornelis, SQL Server MVP|||I have to agree. One more thing - you'll have to drop any constraints or
indexes that use the columns before you can alter the columns. You'll then
have to replace them when you're done.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:p36s72ht36u3slffgfilhi2ic2lhp689tn@.
4ax.com...
On Tue, 30 May 2006 16:05:30 -0700, YC wrote:

>Hi,
>We need to change all Int data types to Bigint. Considering the number of
>tables and View tables, changing manually one by one is not a option. Is
>there a nicer way of changing all (hundreds of them) in more automatic way?
Hi YC,
Tom told you how to do this. But I just have to ask why you want to do
this. I can imagine that the -2,147,483,648 to 2,147,483,647 range is
insufficient for SOME columns - but all'?
Have you considered the impact that this change will have on your DB's
storage requirements? All int columns will double in size - this will
impact storage requirement for tables with integer columns, but for
indexes on integer columns as well (and any nonclustered indexes if the
clustered index of the same table is on an integer column).
How about performance - more bytes per data row (and per index row)
means less rows per page. Means more logical page reads. But also less
cache hits, and hence more physical page reads.
I recommend you to change just the columns that need the ennhanced range
of bigint. Keep other columns as int.
Hugo Kornelis, SQL Server MVPsql