Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB.net remove all primary keys from SQL Table

Posted on 2014-12-23
6
Medium Priority
?
315 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:Murray Brown
6 Comments
 
LVL 36

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 70

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 36

Expert Comment

by:ste5an
ID: 40515262
hmm, need new glasses. I really read tables..(
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Murray Brown
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 2000 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:Murray Brown
ID: 40521894
thanks
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

564 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