Adding a new primary key ID column to an existing table

Question

I want to add a new column to a table. The table is already populated with data.

I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it.

I can create a new table thus:

create table myTestTable
(
    ID uniqueidentifier NOT NULL,
    SomeId uniqueidentifier NOT NULL,
    SomeOtherId uniqueidentifier NOT NULL,
    constraint table_constraint PRIMARY KEY (ID)
)

-- modify the script below to copy the old table across to the new one,
-- generating a unique ID (GUID) into

insert into myTestTable
(ID,
ClaimPaymentId,
TemplateId)
select
    NEWID(),
    SomeId ,
    SomeOtherId 
from [dbo].[TableToBeCopied]

I can then drop the copied table and rename the copy to the old table name.

But I have to do about sixty such tables and I want to write a script which will take a table's name as a parameter and automatically generate the column definitions. But I can't see how to do this. Any ideas?

Answer 1

You can add your GUID ID column with the following.

ALTER TABLE [yourSchema].[yourTable] add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL

To do this for multiple tables you could use something like this:

SELECT 'ALTER TABLE '+QUOTENAME(s.name)+'.'+QUOTENAME(t.name)+' add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL'

FROM sys.schemas s
JOIN sys.tables t ON t.schema_id = s.schema_id

WHERE (     (s.name = 'Schema1' AND t.name IN ('Schema1_TableList'))
            OR
            (s.name = 'Schema2' AND t.name IN ('Schema2_TableList'))
            ... -- Add more schemas and their tables as needed
        )

This will give you all the ALTER TABLE statements to create an ID UNIQUEIDENTIFER coulmn with the ID values pre populated.

Answer 2

I supposed the Primary Key always contains 1 column but you can easily add 1 or more columns pkcol1, pkcol2, ... to the list.

Note that it is usually a bad idea to use a GUID as a Primary Key or Clustered Index because it does not create consecutive numbers (with NEWID())and it is not a narrow value. NEWSEQUENTIALID() can be used to create sequential GUIDs although it is still a wide PK (16 bytes).

Create Statements

SELECT 'ALTER TABLE '+QUOTENAME(schemaname)+'.'+QUOTENAME(tablename)+' ADD '+QUOTENAME(pkcol)+' UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_'+tablename+' DEFAULT newid();
ALTER TABLE '+QUOTENAME(schemaname)+'.'+QUOTENAME(tablename)+' ADD CONSTRAINT PK_'+tablename+'_'+pkcol+' PRIMARY KEY CLUSTERED ('+QUOTENAME(pkcol)+');
'
FROM (values('schema', 'table1', 'id')
            , ('schema', 'table2', 'id')
            , ('schema', 'table3', 'id')
            , ('...', '...', '...')
            , ...
) as tableList(schemaname, tablename, pkcol);

• The output gives you a list of all the ALTER statements for all the tables in the list and they can then be checked out, copied and executed together or one by one

  ALTER TABLE [schema].[table1] ADD [id] UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_table1 DEFAULT newid();
  ALTER TABLE [schema].[table1] ADD CONSTRAINT PK_table1_id PRIMARY KEY CLUSTERED ([id]);
  ...
  

• Adapt the ALTER statements in the SELECT to your needs (CLUSTERED or NONCLUSTERED, table name, constaint name, ...)

• More table schemas, names and PK cols can be added to the list
• You can avoid errors by making sure the table names exist with a JOIN on name between tableList and sys.tables

Print or Execute

Using the same query with a @sql variable:

DECLARE @sql nvarchar(max) = ''

SELECT @sql = @sql + 'ALTER TABLE '+ QUOTENAME(schemaname) +'.'+QUOTENAME(tablename)+' 
    ADD CONSTRAINT PK_'+ tablename +'_'+ pkcol +' PRIMARY KEY CLUSTERED ('+ QUOTENAME(pk) +');
'
FROM ... ;

You can then print the first 4000 characters with:

Print @sql;

ALTER TABLE [schema].[table1] ADD [id] UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_table1 DEFAULT newid();
ALTER TABLE [schema].[table1] ADD CONSTRAINT PK_table1_id PRIMARY KEY CLUSTERED ([id]);
...

Or execute all the ALTER statements with:

EXEC sp_executesql @sql;