SQL deletes the primary keys of all user data tables in the database.

-- SQL Delete primary keys of all user data tables in the database -- Query all the constraints of the current database before deletion select * frominformation_schema.key_column_usagede

-- SQL Delete primary keys of all user data tables in the database -- Query all the constraints of the current database before deletion select * from information_schema.key_column_usage de

Homepage → Database Technology

Background:

Read News

SQL deletes the primary keys of all user data tables in the database.

[Date: 2012-11-24] Source: Linux community Author: xqf222 [Font:]

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

0

SQL creates a primary key for all user data tables in the database

SQL deletes the Foreign keys of all user data tables in the database

SQL statements for related information

Image Information

SQL built-in functions

View All comments in this article (0)

Comments

Latest Information

Popular this week

Introduction to Linux community-advertisement service-website map-help Info-contact us
The articles published on this site (LinuxIDC) do not mean that they agree with the statement or description. They only provide more information and do not constitute any suggestions.


Copyright©2006-2011 Linux community All rights reserved Zhejiang ICP backup No. 06018118

,