Expanding column size

[tha...@bcigroup.com]

Does anyone know how to expand a column size from a char(4) to a char
(6) in SQL Server 6.5?

Sent via Deja.com http://www.deja.com/
Before you buy.

[Neil Pike]

> Does anyone know how to expand a column size from a char(4) to a char
> (6) in SQL Server 6.5?

Q. How can I add/amend/delete columns in SQL Server? E.g. Int to Char,
char(5) to char(15), change NULL to NOT NULL etc.

A. Under SQL 7.0 all the above are easily done with standard ANSI "ALTER
TABLE" ddl commands. Or they can be done via the gui or supplied
stored-procedures.

With SQL 6.5 and below it is only possible to ADD a nullable column - or an
IDENTITY column which seems to work even if it's not NULLable. For *any* other
change a new table must be created, the data copied across, and the tables
renamed around.

Certain 3rd party tools provide a gui interface to do this, that makes it look
transparent, however they are really doing all the work described above, so if
you make the change to a large table it will take a long time to do the work.

Examples of tools are :-

Microsoft's Visual Database Tools (part of Visual Interdev Enterprise
Edition)
SQL Programmer from www.sfi-software.com
XCase - www.xcase.com
Desktop DBA - www.platinum.com
Speed Ferret

Note that there have been reports of Microsoft VDT losing data if you amend
columns on a table and SQL does not have enough free-space to complete the
task.

===

v1.07 1999.06.24
Applies to SQL Server versions : All
FAQ Categories : Database Administration
Related FAQ articles : n/a
Related Microsoft Kb articles : n/a
Other related information : n/a
Authors : Neil Pike
Neil Pike MVP/MCSE. Protech Computing Ltd
(Please reply only to newsgroups)
SQL FAQ (428 entries) see
forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
or www.ntfaq.com/sql.html
or www.sql-server.co.uk
or www.mssqlserver.com/faq