-- SQL creates a primary key for all user data tables in the database
-- Primary key Description: The name is ID, and the data type is Integer auto-increment.
-- Query all constraints of the current database before creation
Select * From information_schema.key_column_usage
Declare @ tablename nvarchar (250)
Declare @ columnname nvarchar (250)
Set @ columnname = 'id'
-- Declare that the cursor mycursor1 is used to read all data tables in the database
Declare mycursor1 cursor for select name from DBO. sysobjects where objectproperty (ID, 'isusertable') = 1
-- Open the cursor
Open mycursor1
-- Retrieve the data value from the cursor and assign it to the data table name variable we just declared.
Fetch next from mycursor1 into @ tablename
-- If the cursor is successfully executed
While (@ fetch_status = 0)
Begin
-- If a primary key Column exists
If exists (select * From syscolumns where id = object_id (n' ['+ @ tablename +'] ') and
Name = ''+ @ columnname + '')
Begin
Exec ('alter table ['+ @ tablename +'] Drop Column
'+ @ Columnname + '')
End
Exec ('alter table ['+ @ tablename +'] add' + @ columnname + '[int] identity (1, 1) not null primary key ')
-- Use a cursor to retrieve the next record
Fetch next from mycursor1 into @ tablename
End
-- Close the cursor
Close mycursor1
-- Undo cursor
Deallocate mycursor1
-- Query all constraints of the current database after creation
Select * From information_schema.key_column_usage