changing column size on publisher side table

Hello,
If I want to change the column size of a table under replication on the
publisher side, can I do this manually? And then manually change the column
size of the corresponding table on the Subscriber side? Or does this require
the use of Replication SP's? If so, what Replication SPs would I need to use
to change a column size?
Thanks,
Rich
Rich - this largely depends on what version of SQL Server you are using:
http://www.replicationanswers.com/AlterSchema2005.asp
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply. I am using Sql Server 2000 Standard Edition, and the
table is under Merge Replication. I wasn't clear on if I could manually
change the size. It looks like I need to use the sp_repladdcolumn and
sp_repldropcolumn to accomplish this. I have, in fact, used these SPs to
add a new column. But I am just checking if I need to use them to change the
size of the column. Yes, No, my choice?
Thanks,
Rich
"Paul Ibison" wrote:

> Rich - this largely depends on what version of SQL Server you are using:
> http://www.replicationanswers.com/AlterSchema2005.asp
> http://www.replicationanswers.com/AddColumn.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

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

changes across all dbs

I need to find the best way to make changes to all metadata my databases.

1. We can do it manually :(
2. We can use merge replication (needs investigation)
3. We can write our own scripts.
search all instances for a metadata database
if db exists check to see if the change exists (ie. new constraint, new column,cell value)
if it doesn't exist ALTER TABLE
send a mail back to DBA for each change made and where

What do you think is the best way?

-Kevinyou can use 3rd party software from www.red-gate.com