Here’s an example of how to use information_schema tables to automatically add clustered primary surrogate keys

select ‘alter table ‘ +  table_name + ‘ add ‘ + table_name + ‘_Id int identity;’ from INFORMATION_SCHEMA.tables t

where not exists (select 0
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
where c.is_identity = 1 and o.object_id = object_id(t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME))
and t.TABLE_TYPE = ‘BASE TABLE’

union all

select ‘alter table ‘ + table_name + ‘ add constraint ‘ + table_name + ‘_pk primary key clustered (  ‘ + table_name + ‘_id );’ from INFORMATION_SCHEMA.tables t

where not exists (select 0
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
where c.is_identity = 1 and o.object_id = object_id(t.TABLE_SCHEMA + ‘.’ + t.TABLE_NAME))
and t.TABLE_TYPE = ‘BASE TABLE’

This will generate the alter commands and the primary keys as shown below if we had tables SalesOffice, Address, SalesRep, CostCenter and they did not have identity columns already.

The not exists check verifies if the table already has a surrogate identity and only create if needed. That would just be done by adding a where to check for existence of an identity column

alter table SalesOffice add SalesOffice_Id int identity;

alter table Address add Address_Id int identity;

alter table SalesRep add SalesRep_Id int identity;

alter table CostCenter add CostCenter_Id int identity;

alter table Address add constraint Address_pk primary key clustered (  Address_id );

alter table SalesRep add constraint SalesRep_pk primary key clustered (  SalesRep_id );

alter table CostCenter add constraint CostCenter_pk primary key clustered (  CostCenter_id );

alter table SalesMgr add constraint SalesMgr_pk primary key clustered (  SalesMgr_id );