Find Foreign Keys By Query and Delete them by Query

Question

HI, I have a table with 7 fields. And 5 field from them are foreign key. I want the list of foreign keys. And Delete them by query. I don't want to delete them from management studio. Is it possible? How?

Code Alone · Answer

Hi, To find the list of foreign keys on a table, use below query : SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'tableName' AND TABLE_SCHEMA = 'dbo' When you find keys then you can delete them by below query : IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = ' tableName ' AND TABLE_SCHEMA = 'dbo' ) BEGIN DECLARE @CONSTRAINT_NAME NVARCHAR(1000) SELECT TOP 1 @CONSTRAINT_NAME=CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = ' tableName ' AND TABLE_SCHEMA = 'dbo' DECLARE @sql NVARCHAR(1000) = 'ALTER TABLE tableName DROP CONSTRAINT ' + @CONSTRAINT_NAME; EXEC (@sql); END You can use it iterative or find the Number of Keys.

Code Alone · Answer

Hi, I wrote an article on same issue today at https://www.c-sharpcorner.com/article/find-primary-unique-foreign-keys-and-delete-them/ This is very detailed article with example.

Nikki · Answer

Thanks. I found the answer from https://www.c-sharpcorner.com/forums/delhi-primary-and-foreign-key-from-the-form-side This tells me to delete Primary Key also.

Abhinay Reddy Deside · Answer

You can get list of Foreign Keys from a table by using a system function. EXEC sp_fkeys 'TableName' To drop a foreign Key you need use Drop Constraint. alter table ' TableName' drop constraint 'TableName_Column_FK' Then Drop The Column alter table 'TableName' drop column 'ColumnName'