How do to massively alter primary keys to tables in sql server database

Question

Hi

Is there any way to massively alter primary keys(uid) to all tables with no primary keys in sql server database ? I have a lot of tables and most of them don not have primary keys.

Thanks in advance.

Answer 1

Add an identity field to the table

Alter Table TableName
Add TableId Integer Identity (1, 1)

Here is how to add the Primary Key

ALTER TABLE TableName
ADD CONSTRAINT pk_TableKeyName PRIMARY KEY (TableID)

To generate this script for each table in your database, you can use one of the following

• The view Information_Schema.Tables
• Undocumented Stored Procedure sp_MSforeachtable