How to drop all Primary Keys from all the tables in SQL Server Database - SQL Server / TSQL Tutorial Part 65

Scenario:

You are working as SQL Server Developer, You have a database that has Primary Key Constraints on almost all the tables. You need to use this database as staging database for ETL process. You really don't need Primary Key Constraint anymore. How would you drop all the Primary Key Constraints for all the table in Database?

Solution:

We will get the list of Primary Key Constraint by using System Views. Once we have the information we will use the Cursor to loop through list of Primary Key Constraint and drop them by using below script.

The general script to drop Primary Key Constraint is 

Alter Table SchemaName.TableName

Drop Constraint ConstraintName

Here is our script to drop all the Primary Key Constraints from a Database

USE YourDatabaseName
GO
--Declare Variables
DECLARE @DatabaseName AS VARCHAR(128)
DECLARE @SchemaName AS VARCHAR(128)
DECLARE @TableName AS VARCHAR(128)
DECLARE @ColumnName AS VARCHAR(128)
DECLARE @ConstraintName AS VARCHAr(128)

DECLARE CUR CURSOR
FOR
--Get Primary Key Constraint
Select
   TC.Table_Catalog as DatabaseName,
   TC.Table_Schema AS TableSchema,
   TC.Table_Name AS TableName,
   CCU.Column_Name AS ColumnName,
   TC.Constraint_Name AS ConstraintName
  From
   information_Schema.Table_Constraints TC  
INNER JOIN
   Information_Schema.constraint_column_usage CCU  
      on TC.Constraint_Name=CCU.Constraint_Name  
      and TC.Table_Name=CCU.Table_Name  
where
   Constraint_Type='PRIMARY KEY'

OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName,@SchemaName,@TableName,@ColumnName,
@ConstraintName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Build dynamic sql for each database 
    DECLARE @SQL VARCHAR(MAX) = NULL
   SET @SQL ='Alter table ['
   SET @SQL+=@SchemaName+'].['+@TableName+']'
   SET @SQL+=' Drop Constraint ['
   SET @SQL+=@ConstraintName+']'
    PRINT @SQL
    EXEC (@SQL)
FETCH NEXT
    FROM Cur
    INTO @DatabaseName,@SchemaName,@TableName,@ColumnName,
@ConstraintName
END
CLOSE Cur
DEALLOCATE Cur

I executed above script on one of my database, it printed out alter statements for drop Primary Key Constraint for all the tables in database and executed.

How to drop all Primary Key Constraints for a SQL Server Database - SQL Server Tutorial