From: esquel@sommarskog.se   
      
   Smith (no@thank.you) writes:   
   > I've got databases (both SQL Server 2000 and 2005) wherein an ID column is   
   > used in many tables. I now need to extend this field from nvarchar(20) to   
   > nvarchar(32). It's nvarchar since it contains both numbers and letters.   
      
   Ouch! This will be painful!   
      
   > How do I go about this?   
   >   
   > Do I drop PKs, FKs and indexes, alter the columns to nvarchar(32) and   
   > re-create PKs, FKs and indexes?   
      
   That is one approach. There are some variations, but this is probably   
   the way to go.   
      
   > I've read about disabling constraints. It isn't possible to disable PKs?   
   > But is it okay to disable FKs and alter column which is part of the FK?   
      
   You cannot disable PKs. And you cannot just disable an FK and fiddle   
   with the PK columns.   
      
   > Dropping / disabling PKs, FKs and indexes will not impact the content of   
   > the tables?   
      
   It will not.   
      
   > Is there an easy way to generate the script that will make the changes   
   > or do I need to "write it by hand"? Is it possible to use "Auto generate   
   > change scripts" feature?   
      
   You can use the Table Designer, but this tool is crappy and the script   
   it generates needs a lot of post-processing to be safe. It will also   
   create new tables and copy data over rather than using ALTER TABLE. But   
   you could this this script as a starting point. That is, make the   
   changes, generate the script and then all commands to drop and recreate   
   constraints from it.   
      
   Take out everything about transactions (see below). Also make sure   
   to change all WITH NOCHECK to WITH CHECK. You want all constraints to   
   be rechecked when you recreate them, so that the optimizer can trust   
   them.   
      
      
   You also need to decide on a disaster strategy. If your script fails half-   
   way through, are you content with reloading a backup? Or is it a   
   requirement that everything is rolled back? I would recommend the   
   former, because that will help to the keep the transaction log in check.   
   And making the script transaction-safe takes extra skill and effort.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)