It's not DROP PRIMARY KEY
it's DROP CONSTRAINT {Object Name}
. For example:
CREATE TABLE dbo.YourTable (ID int NOT NULL);
GO
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (ID);
GO
ALTER TABLE dbo.YourTable DROP CONSTRAINT PK_YourTable;
GO
DROP TABLE dbo.YourTable;
This is why it's so important to explicitly name your objects, as shown above, as you now don't know what the name of the CONSTRAINT
is. You could, however, get the name with the following:
SELECT kc.[name]
FROM sys.key_constraints kc
JOIN sys.tables t ON kc.parent_object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = t.schema_id
WHERE s.[name] = N'dbo'
AND t.[name] = N'YourTable'
AND kc.[type] = 'PK';
If you really didn't want to find out the name and then write the statement, you could use a dynamic statement:
DECLARE @SQL nvarchar(MAX);
SET @SQL = (SELECT N'ALTER TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N' DROP CONSTRAINT ' + QUOTENAME(kc.[name]) + N';'
FROM sys.key_constraints kc
JOIN sys.tables t ON kc.parent_object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = t.schema_id
WHERE s.[name] = N'dbo'
AND t.[name] = N'YourTable'
AND kc.[type] = 'PK');
EXEC sys.sp_executesql @SQL;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…