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

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.