Alter Column datatype with primary key

Question

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.

I was hoping to declare a cursor to get the table names as follows:

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

and then edit the type as follows:

ALTER TABLE @TableName ALTER COLUMN ReferenceID VARCHAR(8)

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).

I really don't want to have to drop and recreate each PK manually for each table.

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?

Answer 1

You need to specify NOT NULL explicitly in an ALTER TABLE ... ALTER COLUMN otherwise it defaults to allowing NULL. This is not permitted in a PK column.

The following works fine.

CREATE TABLE p
(
ReferenceID VARCHAR(6) NOT NULL PRIMARY KEY
)

INSERT INTO p VALUES ('AAAAAA')

ALTER TABLE p ALTER COLUMN ReferenceID VARCHAR(8) NOT NULL

when the NOT NULL is omitted it gives the following error

Msg 5074, Level 16, State 1, Line 1
The object 'PK__p__E1A99A792180FB33' is dependent on column 'ReferenceID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ReferenceID failed because one or more objects access this column.

A couple of things to consider in your programmatic approach is that you would need to drop any foreign keys referencing the ReferenceID columns temporarily and also make sure you don't include the NOT NULL for (Non PK) ReferenceID columns that currently are nullable.

Answer 2

EDIT This solution is needed if you have a muddled database with a mixture of varchar(6) and char(6) columns caused by development extending over 10 years (with enough changes of government policy to cause any attempt at "good database design" to collapse eventually.) END EDIT

To those who said I would have to drop and recreate the PK, you were right. Indexes and Foreign Keys also needed dropping and recreating.

Fortunately, there were a manageable number of indexes and FKs so I handled these as 'exceptional' and dropped them all, one at a time, at the beginning of the script, then re-added them, one at a time, at the end of the script (see the two sections in /* */ below).

The main body of the SQL script then tips complete details about the FKs into a temporary table, then loops through each table name, dropping the FK, altering the datatype, re-adding the FK.

The SQL strings that get assembled are PRINTed in the script below. If you intend to reuse this (no warranties provided, etc., blah blah), comment these out to knock up to 50% off the execution time.

SET NOCOUNT ON

/* Handle exceptional tables here
 * Remove indexes and foreign keys
 * --Lots of "IF EXISTS ... ALTER TABLE <name> DROP CONSTRAINT <constraint name>, etc.
 */

--Declare variables
DECLARE @SQL                    VARCHAR(8000)
DECLARE @TableName              VARCHAR(512)
DECLARE @ConstraintName         VARCHAR(512)
DECLARE @tColumn                VARCHAR(512)
DECLARE @Columns                VARCHAR(8000)
DECLARE @IsDescending           BIT

--Set up temporary table
SELECT
    tbl.[schema_id],
    tbl.name AS TableName,
    i.NAME AS IndexName,
    i.type_desc,
    c.[column],
    c.key_ordinal,
    c.is_desc,
    i.[object_id],
    s.no_recompute,
    i.[ignore_dup_key],
    i.[allow_row_locks],
    i.[allow_page_locks],
    i.[fill_factor],
    dsi.type,
    dsi.name AS DataSpaceName
INTO #PKBackup
FROM 
    sys.tables AS tbl
    INNER JOIN sys.indexes AS i
        ON (
            i.index_id > 0
            AND i.is_hypothetical = 0
        )
        AND ( i.[object_id] = tbl.[object_id] )
    INNER JOIN (
        SELECT
            ic.[object_id] ,
            c.[name] [column] ,
            ic.is_descending_key [is_desc],
            ic.key_ordinal
        FROM
            sys.index_columns ic
            INNER JOIN
                sys.indexes i
                ON
                i.[object_id] = ic.[object_id]
                AND
                i.index_id = 1
                AND
                ic.index_id = 1
            INNER JOIN
                sys.tables t
                ON
                t.[object_id] = ic.[object_id]
            INNER JOIN
                sys.columns c
                ON
                c.[object_id] = t.[object_id]
                AND
                c.column_id = ic.column_id
        ) AS c
        ON c.[object_id] = i.[object_id]
    LEFT OUTER JOIN
        sys.key_constraints AS k
        ON
        k.parent_object_id = i.[object_id]
        AND
        k.unique_index_id = i.index_id
    LEFT OUTER JOIN
        sys.data_spaces AS dsi
        ON
        dsi.data_space_id = i.data_space_id
    LEFT OUTER JOIN
        sys.xml_indexes AS xi
        ON
        xi.[object_id] = i.[object_id]
        AND
        xi.index_id = i.index_id
    LEFT OUTER JOIN
        sys.stats AS s
        ON
        s.stats_id = i.index_id
        AND
        s.[object_id] = i.[object_id]
WHERE
    k.TYPE = 'PK'

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

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT('--Updating ' + @TableName + '...')

    SELECT @ConstraintName = PK.CONSTRAINT_NAME
    FROM
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    WHERE
        PK.TABLE_NAME = @TableName
        AND
        PK.CONSTRAINT_TYPE = 'PRIMARY KEY'

--drop the constraint
    --Some tables don't have a PK defined, only do the next bit if they do
    IF (SELECT COUNT(*) FROM #PKBackup PK WHERE PK.TableName = @TableName) > 0
    BEGIN
        SET @SQL = 'ALTER TABLE @TableName DROP CONSTRAINT @ConstraintName'
        SET @SQL = REPLACE(@SQL, '@TableName', @TableName)
        SET @SQL = REPLACE(@SQL, '@ConstraintName', @ConstraintName)
        PRINT @SQL
        EXEC (@SQL)
    END
--This is where we actually change the datatype of the column
    SET @SQL = 'ALTER TABLE @TableName ALTER COLUMN ReferenceID VARCHAR(8)' + (SELECT CASE WHEN C.Is_Nullable = 'NO' THEN ' NOT NULL' ELSE '' END
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.TABLE_NAME = @TableName AND C.COLUMN_NAME = 'ReferenceID')
    SET @SQL = REPLACE(@SQL, '@TableName', @TableName)

    PRINT(@SQL)
    EXEC(@SQL)

--Recreate the constraint
    --Some tables don't have a PK defined, only do the next bit if they do
    IF (SELECT COUNT(*) FROM #PKBackup PK WHERE PK.TableName = @TableName) > 0
    BEGIN
    --First set up @SQL template
    SELECT @SQL =   'ALTER TABLE [' + SCHEMA_NAME(PK.schema_id) + '].[' + PK.TableName
                    + '] ADD CONSTRAINT [' + PK.IndexName
                    + '] PRIMARY KEY ' + Type_desc + ' ( @Columns ) WITH '
                    + '( PAD_INDEX = ' + CASE   WHEN CAST(INDEXPROPERTY(pk.[object_id], PK.IndexName, N'IsPadIndex') AS BIT) = 0 THEN 'OFF'
                                                ELSE 'ON'
                                            END + ', '
                    + 'STATISTICS_NORECOMPUTE = ' + CASE    WHEN pk.no_recompute = 0 THEN 'OFF'
                                                            ELSE 'ON'
                                                        END
                    + ', SORT_IN_TEMPDB = OFF, '
                    + 'IGNORE_DUP_KEY = ' + CASE    WHEN pk.[ignore_dup_key] = 0 THEN 'OFF'
                                                    ELSE 'ON'
                                                END + ', '
                    + 'ONLINE = OFF, '
                    + 'ALLOW_ROW_LOCKS = ' + CASE   WHEN pk.allow_row_locks = 0 THEN 'OFF'
                                                    ELSE 'ON'
                                                END + ', '
                    + 'ALLOW_PAGE_LOCKS = ' + CASE  WHEN pk.allow_page_locks = 0 THEN 'OFF'
                                                    ELSE 'ON'
                                                END + ', '
                    + 'FILLFACTOR = ' + CASE    WHEN pk.[fill_factor] = 0 THEN '100'
                                                ELSE CONVERT(NVARCHAR, pk.[fill_factor])
                                            END + ' '
                    + ') ON [' + CASE   WHEN 'FG' = pk.[type] THEN pk.DataSpaceName
                                        ELSE N''
                                    END + ']'
    FROM
    #PKBackup PK WHERE PK.TableName = @TableName

    SET @SQL = REPLACE(@SQL, '@TableName', @TableName)
    SET @SQL = REPLACE(@SQL, '@ConstraintName', @ConstraintName)

    --Second, build up @Columns
    SET @Columns = ' '
    DECLARE ColumnCursor CURSOR LOCAL READ_ONLY FOR
        SELECT pk.[column], PK.is_desc
            FROM #PKBackup PK 
            WHERE PK.TableName = @TableName
            ORDER BY PK.key_ordinal ASC

    OPEN ColumnCursor
        FETCH NEXT FROM ColumnCursor 
        INTO @tColumn, @IsDescending

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Columns = @Columns + @tColumn + CASE WHEN @IsDescending = 1 THEN ' DESC, ' ELSE ' ASC, ' END

        --Get the next TableName
        FETCH NEXT FROM ColumnCursor 
        INTO @tColumn, @IsDescending
    END

    --Tidy up
    CLOSE ColumnCursor
    DEALLOCATE ColumnCursor

    --Delete the last comma
    SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)
    END
--Recreate the constraint
    SET @SQL = REPLACE(@SQL, '@Columns', @Columns)
    PRINT @SQL
    EXEC (@SQL)

    PRINT('--Done
    ')

    SET @SQL = ''

--Get the next TableName
    FETCH NEXT FROM TableCursor 
    INTO @TableName
END

--Tidy up
CLOSE TableCursor
DEALLOCATE TableCursor

DROP TABLE #PKBackup

/* Handle exceptional tables here
 * Replace indexes and foreign keys that were removed at the start
 */

SET NOCOUNT OFF