问Server更改主键数据类型

EN

ALTER TABLE t1  
DROP CONSTRAINT PK__t1__3213E83F88CF144D;   
GO  

alter table t1 
alter column id varchar(10) not null

alter table t1 add primary key (id)

ALTER TABLE table_name
 DROP CONSTRAINT constraint_name;

ALTER TABLE  table_name
ALTER COLUMN pk_column_name target_data_type(size) not null;

ALTER TABLE table_name
ADD PRIMARY KEY (pk_column_name);

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WITH (NOLOCK) WHERE C.TABLE_CATALOG = '<<DB>>' AND C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME = '<<Table>>' 
AND C.COLUMN_NAME = '<<COLUMN>>' AND C.DATA_TYPE = 'int') -- <- Additional test to check the current datatype so this won't make unnecessary or wrong updates
BEGIN
    DECLARE @pkName VARCHAR(200);
    SELECT @pkName = pkRef.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkRef WITH (NOLOCK)
    WHERE pkRef.TABLE_CATALOG = '<<DB>>' AND pkRef.TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '<<Table>>'

    IF(@pkName IS NOT NULL)
    BEGIN
        -- Make sure the primary key name is the one you are going to use in script beyond this point.
        IF(@pkName != '<<PRIMARY KEY NAME>>') 
        BEGIN
            RAISERROR ('Unexpected primary key name - The primary key found has a different name than expected. Please update the script.', 16, 1);
            RETURN;
        END

        ALTER TABLE dbo.<<Table>>
              DROP CONSTRAINT <<PRIMARY KEY NAME>>; -- Note: this is not a string or a variable (just type the PK name)
        SELECT 'Dropped existing primary key';
    END

    ALTER TABLE dbo.<<Table>> ALTER COLUMN ID BIGINT
    SELECT 'Updated column type to big int';

    ALTER TABLE dbo.<<Table>>
          ADD CONSTRAINT <<PRIMARY KEY NAME>> PRIMARY KEY CLUSTERED (<<COLUMN>>);
    SELECT 'Created the primary key';
END
ELSE
BEGIN
    SELECT 'No change required.';
END