-- SQL deletes the primary keys of all user data tables in the database.
-- Query all constraints of the current database before deletion
Select * from information_schema.key_column_usage
Declare @ TableName nvarchar (250)
-- 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
-- Define the current primary key constraint variable
Declare @ ConstraintName varchar (200)
-- Delete all primary key constraints of the current data table
-- Declare to read all primary key constraint names of the data table cursor mycursor2
Declare mycursor2 cursor for select name from dbo. sysobjects where Xtype = 'pk' and Parent_Obj = (select [ID] from dbo. sysobjects where id = object_id (n' ['+ @ TableName +'] ') and OBJECTPROPERTY (id, n'isusertable') = 1)
-- Open the cursor
Open mycursor2
-- Retrieve the data from the cursor and assign values to the primary key constraint name variable.
Fetch next from mycursor2 into @ ConstraintName
-- If the cursor is successfully executed
While (@ fetch_status = 0)
Begin
-- Delete the primary key currently found
Exec ('alter TABLE ['+ @ TableName +'] drop constraint ['+ @ ConstraintName +'] ')
-- Print 'alter TABLE ['+ @ TableName +'] drop constraint ['+ @ ConstraintName +']'
-- Use a cursor to retrieve the next record
Fetch next from mycursor2 into @ ConstraintName
End
-- Close the cursor
Close mycursor2
-- Undo cursor
Deallocate mycursor2
-- 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 deleted Database
Select * from information_schema.key_column_usage