Solved

How to delete a primary key in a table in SQL Server?

Posted on 2008-10-21
2
1,247 Views
Last Modified: 2012-05-05
I have a table: Company and I want to delete its primary ID, but below statement is not working:
#1
ALTER TABLE Company DROP CONSTRAINT pk_Company

When I checked the table properties in SQL Server and use below statement:
#2
ALTER TABLE Company DROP CONSTRAINT pk_Company__725BF7F6
then it will work.

My question is: how to write a script to delete the primary key of a table, just as simple as #1? Since if I want to write a script to be deployed to production server to delete a primary key, I couldn't know the exact name: pk_Company__725BF7F6 before viewing production.

I hope my question is clear and thanks for any help.
0
Comment
Question by:heyday2004
2 Comments
 
LVL 11

Expert Comment

by:Muhammad Kashif
ID: 22765639
You can get the name of primary key constraint

SELECT Constraint_Name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName'

0
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
ID: 22765673

DECLARE @table nvarchar(255);

DECLARE @sql nvarchar(1024);
 

SET @table = 'Company';
 

SET @sql = (select 'ALTER TABLE [' + @table + '] DROP CONSTRAINT [' + [name] + ']'

			  from sys.objects 

			 where parent_object_id in (select object_id 

										  from sys.objects 

										 where [name] = @table)

			   and type='PK');
 

EXEC sp_executesql @sql;

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now