CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
kostepanych
kostepanych

Reputation: 2619

How to drop table PK if I do not know its name

I try to drop primary key:

alter table mytable DROP (
 SELECT CONSTRAINT_NAME FROM information_schema.table_constraints
 where table_name='mytable' and constraint_type = 'PRIMARY KEY')

How to do it correctly?

Upvotes: 1

Views: 56

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

First, don't use INFORMATION_SCHEMA views. Second, always use the schema prefix when referencing objects in DDL or DML. Third, you need to use dynamic SQL, because you can't parameterize DDL that way.

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'ALTER TABLE dbo.mytable DROP CONSTRAINT ' 
  + QUOTENAME(k.name) + ';'
    FROM sys.key_constraints k
INNER JOIN sys.objects AS o
ON k.parent_object_id = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.name = N'mytable'
AND s.name = N'dbo';

PRINT @sql;
-- EXEC sp_executesql @sql;

Now, this won't necessarily work, for example if there are other tables with foreign keys referencing this primary key, you will need to seek those out and fix them first.

Upvotes: 2

Related Questions