Changing data type

Hi all,

I have to extend the length of a field, which appears in hundred of tables in a database, and maybe involved in constraints/index/primary key. It is difficult for me to drop all the constraints/index/primary key before altering each table one by one....Is there any easier way to drop all constraints and keys (instead of dropping them one by one)? Or any better (faster) way to extend the length of this field?

Thanks for any help here!OK, here's one method:

Script out your entire database. Then use search and replace to modify all instances of the field length (character, I assume?). Then use this script to create a new, corrected database, and use DTS to transfer data from the old database.

I hope you are aware, though, that you may likely render much of your SQL code (procedures, functions, triggers...) obsolete if they reference your field and attempt to assign it or concatenate it to variables that are of insufficient length. You may have quite a debugging job ahead of you. A 3rd party package such as ERWIN might be of assistance.

blindman|||Just generate ALTER TABLE statements:

SELECT 'ALTER TABLE ' + T.Name + ' ALTER COLUMN ' + C.Name + ' nVarChar(<YourNewFieldLength>);'
FROM SysColumns C INNER JOIN
SysObjects T ON T.id = C.id
WHERE T.XType = 'U' AND T.Name <> 'dtproperties' AND
C.Name = < YourColumnName >

You can consider to open a cursor, and to execute your statement dynamically. Alternatively, you can also generate a script.

I didn't check it for constraints, but indices are automatically updated by this statement.