drop primary key 1

[scklifasovskiy]

How can drop a primary key of the table without knowing name of the key?

 

[bborissov]

DECLARE @PrimaryKey nvarchar(2000)
SELECT @PrimaryKey = N'ALTER TABLE TableNameHere DROP CONSTRAINT ' + Index_Name
FROM sys.indexes
WHERE is_primary_key = 1 AND Object_Name(Object_Id) = 'YourTableName'

exec sp_executesql @PrimaryKey

Borislav Borissov
VFP9 SP2, SQL Server

 

[scklifasovskiy]

i did it a bit diff.. but same idea :
DECLARE @SQL VARCHAR(4000)
SELECT @SQL = 'ALTER TABLE MULTESC DROP CONSTRAINT ' + name
FROM sysobjects
WHERE xtype = 'PK'
AND parent_obj = OBJECT_ID('MultEsc')
EXEC (@SQL)

Thank you!

 

[TheBugSlayer]

If you are using a version of SQL Server above 2000 you should get in the habit of using the system views in SYS. So instead of sysobjects you should use sys.object (sys.system_objects for system objects or sys.all_objects for system and user objects). But since you are working with an index in particular, why not use the more specific sys.indexes as George suggested?

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

as George suggested?

Wasn't me, but it is what I would have suggested if Boris hadn't answered first.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom