Solved

VB.net remove all primary keys from SQL Table

Posted on 2014-12-23
6
258 Views
Last Modified: 2014-12-29
Hi

What VB.net code would I use to remove all the primary keys from a SQL table?

Thanks
0
Comment
Question by:murbro
6 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40515167
You would include SQL Server Management Objects in your references and use the IndexCollection to find all indices which are primary keys. Then you may be able to delete them as long as no foreign key relationship exists.

Something like
foreach (Table table in database.Tables)
{
    Index primaryKey = table.Indexes
		.FirstOrDefault(index => index.IndexKeyType == IndexKeyType.DriPrimaryKey);
    if (primaryKey != null)
    {
        primaryKey.Drop();
        table.Alter();
    }
}

Open in new window

.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40515221
A given table could have only one primary key, so you don't need to worry about any kind of looping or multiple entries.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40515262
hmm, need new glasses. I really read tables..(
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:murbro
ID: 40516300
I use the following code to add the keys. What similar code would I use to remove them
          Dim oSQL As String
            oSQL = "ALTER TABLE " & oTable
            oSQL = oSQL & " ADD CONSTRAINT pk_" & oTable & " PRIMARY KEY ([" & oColumn1 & "], [" & oColumn2 & "])"
            'oSQL = oSQL & " GO"
            Dim cn As New SqlConnection(Globals.ThisAddIn.oRIGHT.lblConnectionString.Text)
            Dim cmd As New SqlCommand(oSQL, cn)
            cn.Open()
            cmd.ExecuteNonQuery()
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 40521004
I would change:
 oSQL = oSQL & " ADD CONSTRAINT pk_" & oTable & " PRIMARY KEY ([" & oColumn1 & "], [" & oColumn2 & "])"
To:
oSQL = oSQL & " DROP CONSTRAINT pk_" & oTable
0
 

Author Closing Comment

by:murbro
ID: 40521894
thanks
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 40
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 30
SQL Update trigger 5 16
VB.Net Tranfer data between to VB.Net apps 8 25
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

813 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