SQL batch add primary key scripts

-- SQL batch add primary key scripts

-- Operation prompt: Please remember to back up the database you want to operate before running this script
-- Implementation function: After the script is executed, all data tables in the database will have a primary key.
-- Add Rule 1: The primary key name is ID (which can be modified by yourself), and the data type is Integer auto-increment by 1.
-- Add Rule 2: if the data table already has a primary key, do not add
-- Add Rule 3: if the data table does not have a primary key but an ID column has been added, set this ID column as the primary key.
-- Add Rule 4: if the data table has no primary key and an ID column already exists, the column is deleted. Remember to check whether the ID column contains important data. If yes, change the name before executing the script.

-- Script code starts
Declare @ columnname nvarchar (250)
Set @ columnname = 'id' -- Name of the primary key column to be added, which can be modified by yourself

Declare @ tablename nvarchar (250) -- Name of the data table retrieved from the cursor
Declare @ tableid int -- number of the data table retrieved from the cursor
Declare @ identitycolumnname nvarchar (250) -- name of an existing ID column in a data table

-- Declare the cursor for reading all data table names and numbers of the database
Declare mycursor1 cursor for select name, ID from DBO. sysobjects where objectproperty (ID, 'isusertable') = 1 order by name
-- Open the cursor
Open mycursor1
-- Retrieve the data value from the cursor and assign it to the data table name variable and data table number variable we just declared.
Fetch next from mycursor1 into @ tablename, @ tableid
-- If the cursor is successfully executed
While (@ fetch_status = 0)
Begin

-- Determines whether the current data table has a primary key. If the data table already has a primary key, no primary key is added.
If not exists (select * From information_schema.key_column_usage where table_name = ''+ @ tablename + '')
Begin
-- If an ID column already exists in the data table, set the ID column as the primary key.

If exists (select top 1 from sysobjects where objectproperty (ID, 'tablehasauth') = 1 and upper (name) = upper (@ tablename ))
Begin
Select @ identitycolumnname = Name from syscolumns where id = @ tableid and columnproperty (ID, name, 'isidentity ') = 1
Print 'current data table ['+ @ tablename +'] has no primary key but has an ID column ['+ @ identitycolumnname +']'
Exec ('alter table ['+ @ tablename +'] add constraint pK _ '+ @ tablename + 'Primary key clustered (' + @ identitycolumnname + ') on [primary]')
Print 'successfully set the data table ['+ @ tablename +'] The existing ID column ['+ @ identitycolumnname +'] as the primary key'
End
Else
Begin
Print 'current data table ['+ @ tablename +'] No primary key and ID columns'
If exists (select * From syscolumns where id = object_id (n' ['+ @ tablename +'] ') and name = ''+ @ columnname + '')
Begin
-- If an index is set for an ID column, delete all constraints corresponding to the specified field @ columnname in the Data Table @ tablename.
Declare @ constraintname varchar (250) -- defines the constraint variable for the current query

-- Declare all Constraints List cursor corresponding to the specified field in the read data table
Declare mycursor2 cursor for select name from sysobjects left join sysconstraints on sysconstraints. constid = sysobjects. ID where parent_obj = object_id (''+ @ tablename +'') and colid = (select colid from syscolumns where id = object_id (''+ @ tablename + '') and objectproperty (ID, N 'isusertable') = 1 and upper (name) = upper (@ columnname ))
-- Open the cursor
Open mycursor2
-- Retrieve the data from the cursor and assign values to the constraint name variable.
Fetch next from mycursor2 into @ constraintname
-- If the cursor is successfully executed
While (@ fetch_status = 0)
Begin
-- Delete the constraint currently found
Exec ('alter table ['+ @ tablename +'] Drop constraint ['+ @ constraintname +'] ')
Print 'constraints for successfully deleting the ['+ @ tablename +'] field ['+ @ columnname +'] ['+ @ constraintname +'] 'of the data table
-- Use a cursor to retrieve the next record
Fetch next from mycursor2 into @ constraintname
End
-- Close the cursor
Close mycursor2
-- Undo cursor
Deallocate mycursor2

-- If the column [ID] exists, the column is deleted first.
Exec ('alter table ['+ @ tablename +'] Drop column' + @ columnname + '')
-- Add the ID of the primary key column of the data table
Exec ('alter table ['+ @ tablename +'] add' + @ columnname + '[int] identity (1, 1) not null primary key ')
End
Else
Begin
-- If the column [ID] exists, add the primary key column ID directly.
Exec ('alter table ['+ @ tablename +'] add' + @ columnname + '[int] identity (1, 1) not null primary key ')
End

Print 'the data table [' + @ tablename + '] column [' + @ columnname + '] is set as the primary key'
End
End
Else
Begin
Print 'current data table ['+ @ tablename +'] existing primary key'
End
-- Use a cursor to retrieve the next record
Fetch next from mycursor1 into @ tablename, @ tableid
End

-- Close the cursor
Close mycursor1
-- Undo cursor
Deallocate mycursor1
-- Script code ended