Alter Column datatype with primary key

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 1The object 'PK__p__E1A99A792180FB33' is dependent on column 'ReferenceID'.Msg 4922, Level 16, State 9, Line 1ALTER 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.

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 variablesDECLARE @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 tableSELECT    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 DataSpaceNameINTO #PKBackupFROM     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 @TableNameWHILE @@FETCH_STATUS = 0BEGIN    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 @TableNameEND--Tidy upCLOSE TableCursorDEALLOCATE TableCursorDROP TABLE #PKBackup/* Handle exceptional tables here * Replace indexes and foreign keys that were removed at the start */SET NOCOUNT OFF

You need to execute the ALTER statement as dynamic SQL: Build the statement as a SQL string and pass it to sp_executesql.