Removing All Primary Keys

Question

This is going to sound like a crazy request. The databases that I report from do not have any foreign keys, and every single primary key is an identity_column. This makes using tools such as TOAD difficult because the Intellisense works by reading the PK and FK relationships.

Anyone have a script to remove the primary keys from every table in the database so I can replace them with "correct" PK and add FK’s to assist in reporting?

To head off the avalanch of "Don't do it!!!" responses, let me make it clear that I am not going to do this to my production database, but copy of it on another server.

Any advice would be appreciated.

------- EDIT This is updated with correct information. ----------------

Thanks guys, but I realized I've made a mistake. Almost every single table has an "identity_column" with the property of identity. That identity is a clustered index. However, it is not designated as a primary key.

First, what is the difference between a primary key and a clustered index?

Second, how can I script out all the clustered indexes? Would this work?

SELECT 
  'ALTER TABLE ' + OBJECT_NAME(OBJECT_ID) + ' DROP CONSTRAINT ' + name 
FROM sys.indexes WHERE type_desc = 'CLUSTERED'

Thanks for your patience

Answer 1

How about something like this?

[Untested]

-- Helper Procedure 
CREATE PROC #DropConstraints 
  @tableSchema nvarchar(max), 
  @tableName nvarchar(max), 
  @constraintType nvarchar(20) 
AS 
BEGIN 
  DECLARE @cName nvarchar(max); 

  DECLARE constraint_cursor CURSOR FOR 
    SELECT CONSTRAINT_NAME  
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE  
      CONSTRAINT_TYPE = @constraintType 
      AND TABLE_NAME = @tableName 
      AND TABLE_SCHEMA = @tableSchema 

  OPEN constraint_cursor 

  FETCH NEXT FROM constraint_cursor INTO @cName 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
    EXEC ('ALTER TABLE ' + @tableSchema + '.' + @tableName + ' DROP CONSTRAINT ' + @cName); 
    FETCH NEXT FROM constraint_cursor INTO @cName 
  END 

  CLOSE constraint_cursor 
  DEALLOCATE constraint_cursor 
END 
GO

BEGIN TRANSACTION

  -- Setup Cursor for looping 
  DECLARE table_cursor SCROLL CURSOR FOR 
    SELECT TABLE_SCHEMA, TABLE_NAME  
    FROM INFORMATION_SCHEMA.TABLES 

  OPEN table_cursor

 -- Drop Primary Keys 
  FETCH FIRST FROM table_cursor INTO @tableSchema, @tableName 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
    EXEC #DropConstraints @tableSchema, @tableName, 'PRIMARY KEY'; 

    FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName 
  END 

  -- Cleanup 
  CLOSE table_cursor 
  DEALLOCATE table_cursor

COMMIT TRANSACTION 
GO

DROP PROCEDURE #DropConstraints;
GO

Answer 2

Another option would be a two-step process:

1. first, select the necessary information from the system catalog views, and use those to construct the T-SQL statements that you'll need to actually drop the indices and constraints:

   SELECT
     'ALTER TABLE ' + OBJECT_NAME(OBJECT_ID) + ' DROP CONSTRAINT ' + name
   FROM sys.indexes WHERE is_primary_key = 1
   

2. use that result set, copy & paste that into a new query window, and run it - it will drop all the primary key constraints from all your tables in the database you run this in

That way you're avoiding the cursor, and you get a list of statements to execute, which you can still use "as is", tweak, or even throw away completely, if you don't need it.

Answer 3

To answer your question about the differnce betweeen a PK and a clustered index:

Primary keys are the key values that guarantee the record can be uniquely identified. They have nothing to do with clustered indexes (which dictate the order the records are physically stored in) except that the default for creating a primary key is to make it a clustered index. You however do not have to make it a clustered index.

Be aware that if you have not had primary keys and foreign keys in the past, your data may be thoroughly hosed up and you should not create the foreign keys until you clean it up.

Answer 4

To drop all clustered indexes, you have to differentiate between situations where a constraint (Primary or Unique) is the clustered index or whether a non-constraint index is the clustered index. You cannot drop contraint indexes using DROP INDEX and you cannot drop indexes using DROP CONSTRAINT. So you would need to do something like:

Select 'ALTER TABLE ' + QUOTENAME(OBJECT_NAME([object_id])) + ' DROP CONSTRAINT ' + QUOTENAME([name])
From sys.indexes
Where is_primary_key = 1 Or is_unique_constraint = 1
 And type_desc = 'CLUSTERED'
Union All
Select 'DROP INDEX ' + QUOTENAME([name]) + ' ON ' + QUOTENAME(OBJECT_NAME([object_id])) 
from sys.indexes
Where is_primary_key = 0 And is_unique_constraint = 0
 And type_desc = 'CLUSTERED'

Frankly, even this probably won't work because all foreign keys to any primary keys will have to be dropped before you can drop the primary key. To do it right, you'd want to script all foreign keys, drop them all, then drop all the clustered constraints and then recreate all the foreign keys.

I have to ask whether this is really what you want to do. By dropping all the clustered indexes, you will force a rebuild of all indexes in all tables affected.