Script to auto-add surrogate primary keys

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 );

Advertisements
This entry was posted in SQL Server and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s