SQL Server – Drop Foreign Key Without Knowing Constraint Name

sql-server

I have created one table using the below command:

create table Table1(
    Id int Not Null 
        Foreign key 
        references Table2(Id)  
        on delete cascade 
        on update cascade,
    UserName nvarchar(150),
    TimeInSeconds int Not Null 

    primary key(Id,TimeInSeconds)
);

But now I want to drop the foreign key.
As I haven't given a constraint name, I can't use:

Alter table <tablename>
drop foreign key <foreign key name>

Is there any way?

Best Answer

You can find the name of the constraint in INFORMATION_SCHEMA.TABLE_CONSTRAINTS

select CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = 'Table1'
Related Question