Removing All Primary Keys
Solution 1
How about something like this?
-- 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
-- Declare Variables
DECLARE
@tableSchema nvarchar(max),
@tableName nvarchar(max)
-- 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
Solution 2
Another option would be a two-step process:
-
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
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.
Solution 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.
Solution 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.
DavidStein
I'm a Data Warehouse Architect who utilizes the Microsoft BI Stack.
Updated on June 04, 2022Comments
-
DavidStein almost 2 years
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