Decrease columns from VARCHAR(30) to VARCHAR(20)

Question

Post reply

Decrease columns from VARCHAR(30) to VARCHAR(20)

aravind-305595

Ten Centuries

Points: 1389
More actions
August 23, 2007 at 12:02 am

#118320

Hi,
I have a column name strpropertyname VARCHAR(30) which is available in more than 25 tables. Its Primary Key or part of primary Key in some tables and foreign key in the remaining tables. Is there any script or short-cut method to generate script for altering the columns from VARCHAR(30) to VARCHAR(20). (I am asked to decrease the column length actually.)

Thanks
Regards,
Aravind.

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Since the datatype is the same, you can just use an alter column statement like:

ALTER TABLE [dbo].[alma] ALTER COLUMN [a] [varchar] (30)

However, if the column is part of a primary key, an index, a foreign key, default, computed column, etc, these dependents need to be dropped and recreated.

There are third party tools that can compare schemata and generate the transition script. With these tools you could:

1: compare your current database with an empty database and generate the sync script (this script would create the schema only

2: before executing this script, alter it to change the column lengths

3: execute this script: This will create you a database that has the right schema, but without the data

4: use the comparison tool to generate a script that synchronizes the schema of the new database to the original one. Many of these tools are designed to preserve the data, and since in your case the change can be achieved with a simple alter statement, the scripts will preserve the data, but will drop and create the dependent constraints indexes ( and in the right order)

Of course, I've not mentioned step 0: (make a backup )

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

aravind-305595 Ten Centuries Points: 1389 More actions · Answer 2

Hi Andras, Its a good idea.Thank You Very much.

Regards,

Aravind

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

Andras, your method doesnt seem faster than just making the change in SSMS? Both techniques require a lot of repetition, I'd think less room for error using SSMS to make the change.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 4

Hi Andy,

you are right that SSMS can do this in many cases. (I actually did not know this, but it is nice to learn new things). On the other hand I'm not sure about the limitations of SSMS. After a few minuites of playing with this feature it messed up my schema (among others, it has removed schema binding for example, but did not put it back on any of the dependent views). Do you know its limitations?

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

Why shorten the column? It's varchar, so no savings in space if everything is under 20.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 6

I was wondering about this too, but I thought that well designed schemata have proper domain restrictions. Reducing the size then makes sense. (But I'm just guessing what Aravind's motivation might have been.)

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software