How To Drop All Constraints On A Table In SQL Server

Recently, we had a very critical issue; as an SQL developer, it was pretty challenging for me to fix that issue. After analyzing the problem, I learned we must remove all the constraints related to that issue from the table. So, I have identified a few approaches to do this. In this article, we will thoroughly dive into all the approaches.

Approach-1: Using Drop statement

We can execute the drop statements below to complete this task.

DECLARE @myTableName NVARCHAR(128) = 'AzureProduct'
DECLARE @myTableSchema NVARCHAR(128) = 'dbo'

-- Specify the DROP statements for all the constraints
SELECT 'ALTER TABLE ' + @myTableSchema + '.' + @myTableName + ' DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(@myTableSchema + '.' + @myTableName)

UNION ALL

SELECT 'ALTER TABLE ' + @myTableSchema + '.' + @myTableName + ' DROP CONSTRAINT ' + name
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(@myTableSchema + '.' + @myTableName)

UNION ALL

SELECT 'ALTER TABLE ' + @myTableSchema + '.' + @myTableName + ' DROP CONSTRAINT ' + name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID(@myTableSchema + '.' + @myTableName)

UNION ALL

SELECT 'ALTER TABLE ' + @myTableSchema + '.' + @myTableName + ' DROP CONSTRAINT ' + name
FROM sys.key_constraints
WHERE parent_object_id = OBJECT_ID(@myTableSchema + '.' + @myTableName)

Check out How To Check Table Description In SQL Server

After executing the above query, I got the expected output, as shown in the screenshot below.

Now, let us execute the specific drop statement that we got to remove the particular constraint on the AzureProduct table.

ALTER TABLE dbo.AzureProduct DROP CONSTRAINT PK_AzureProduct

After executing the above query, the constraints have been deleted successfully, as shown in the screenshot below.

Now, to cross-check, we can rerun the script, and this time, we got the no column name as output since the constraints have been deleted successfully.

Check out How To Update Statistics On A Table In SQL Server

Approach-2: Using the sp_help stored procedure

Let us execute the query below to determine the constraints available in a particular table.

EXEC sp_help 'dbo.AzureProduct'

After executing the above script, I got the expected output as shown in the screenshot below.

We can execute the query below to drop the primary key constraint from the AzureProduct table.

ALTER TABLE dbo.AzureProduct DROP CONSTRAINT PK_AzureProduct

After executing the above query, I got the expected output below.

Now, the constraints from the table have been removed successfully, as shown in the screenshot below.

Note: Ensure to take a proper database backup and analyze the impact properly before removing the constraints from your table.

Conclusion

Removing the constraints on a table in SQL Server is easy using the information mentioned in this article, but ensure a proper backup of your database to ensure a smooth transition.

You may also like following the articles below.

• How To Rename The Table Name In SQL Server
• How To Enable CDC On A Table In SQL Server
• How To Create A Table Variable In SQL Server