Find Foreign Keys By Query and Delete them by Query

Nikki

8y899SQL

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?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Code Alone8y ago
Accepted 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.
+1
Code Alone8y ago
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.
+1
Nikki8y ago
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.
+1
Abhinay Reddy Deside8y ago
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'
-1