title: Alter Column datatype with primary key
body: <p>I have a ReferenceID varchar(6) column in over 80 different tables. I need to extend this to a varchar(8) throughout the db following a change implemented by the government organisation that assigns the IDs.</p>
  
  <p>I was hoping to declare a cursor to get the table names as follows:</p>
  
  <pre><code>DECLARE @TableName AS VARCHAR(200)
  DECLARE TableCursor CURSOR LOCAL READ_ONLY FOR
  SELECT t.name AS TableName
      FROM sys.columns c
          JOIN sys.tables t ON c.object_id = t.object_id
      WHERE c.name = 'ReferenceID'
  
  OPEN TableCursor
      FETCH NEXT FROM TableCursor 
      INTO @TableName
  </code></pre>
  
  <p>and then edit the type as follows:</p>
  
  <pre><code>ALTER TABLE @TableName ALTER COLUMN ReferenceID VARCHAR(8)
  </code></pre>
  
  <p>This fails because the column is part of the Primary Key in some of the tables (and the columns included in the PK vary from table to table).</p>
  
  <p>I really don't want to have to drop and recreate each PK manually for each table.</p>
  
  <p>Within the cursor, is there a way either to disable the PK before altering the datatype and then re-enable it, or to drop and recreate the PK either side of altering the datatype, bearing in mind that the PK will depend on which table we're currently looking at?</p>