I have to increase the length of a primary key which appears in several tables. I have to do this programmatically and do it in several databases.
I am almost there, and things work individually, but when I put everything together I get an error on the last line ALTER TABLE table1 ADD PRIMARY KEY(PRIMARYKEYCOLUMN)

Msg 1911, Level 16, State 1, Line 30
Column name ‘PRIMARYKEYCOLUMN’ does not exist in the target table or view.

Msg 1750, Level 16, State 0, Line 30
Could not create constraint or index. See previous errors.

This is the whole query

DECLARE @Col1Len varchar(100) = (SELECT character_maximum_length    
                                 FROM information_schema.columns  
                                 WHERE table_name = 'table1' 
                                   AND column_name = 'Col1')

IF (@Col1Len < 60)
BEGIN 
    DECLARE @PKs TABLE (PRIMARYKEYCOLUMN varchar(100))

    INSERT INTO @PKs (PRIMARYKEYCOLUMN) (
        SELECT column_name AS PRIMARYKEYCOLUMN
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
              ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
              AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME 
              AND KU.table_name = 'table1')

    SELECT * FROM @PKs

    DECLARE @PK varchar(100) = (SELECT name 
                                FROM sysobjects 
                                WHERE xtype = 'PK'
                                  AND parent_obj IN (SELECT id 
                                                     FROM sysobjects 
                                                     WHERE name = 'table1')
                               )

    DECLARE @Command varchar(100) = 'ALTER TABLE table1 DROP CONSTRAINT ' + @PK
    EXECUTE (@Command)  

    ALTER TABLE table1 
        ALTER COLUMN Col1 varchar(40) NOT NULL

    ALTER TABLE table1 
        ADD PRIMARY KEY(PRIMARYKEYCOLUMN)
END

If I run just the block below, it works and it finds the primary keys and inserts them into @PKs Table.

    DECLARE @PKs TABLE (
        PRIMARYKEYCOLUMN varchar(100)
    )
    Insert into @PKs (PRIMARYKEYCOLUMN) (
        SELECT column_name as PRIMARYKEYCOLUMN
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
            ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
            AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME 
            AND KU.table_name='table1'
    )
        SELECT * from @PKs

What am I doing wrong?

>Solution :

You’re getting the error because your alter table statement isn’t related to the @PKs table variable. Also, in case of a composite primary key (meaning, a primary key that is the combination of multiple columns), you need to add it as a comma-separated list of column names –
i.e. alter table add constraint pk_table primary key (col1, col2) To do that, you’ll need to build the alter table dynamically from the values in the @PKs table variable:

DECLARE @AddPK nvarchar(1000);
SELECT @AddPK = 'ALTER TABLE table1 ADD CONSTRAINT PK_table1 PRIMARY KEY(' + STRING_AGG(PRIMARYKEYCOLUMN, ',') +');'
FROM @PKs

ALTER TABLE table1 DROP CONSTRAINT PK_table1;
ALTER TABLE table1 ALTER COLUMN Col1 nvarchar(10) NOT NULL;
EXECUTE(@AddPK);

Note: String_Agg requires SQL Server 2017 (14.x) or later, if you’re using an older version you’ll have to use the old workaround of for xml + stuff to concatenate rows into a comma delimited string.

And a side note – instead of varchar(100) for PRIMARYKEYCOLUMN use sysname.

sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it isn’t nullable. sysname is used to reference database object names.