Solved

VB.net Get a count of the number of primary keys in a SQL table

Posted on 2014-11-10
3
234 Views
Last Modified: 2014-11-10
Hi

I use the following code to get the primary key for a SQL table. If the table has more than one primary key
how do I get a count of the number of primary keys? Thanks

            sSQL = "SELECT column_name "
                sSQL = sSQL & "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE "
                sSQL = sSQL & "WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1"
                sSQL = sSQL & "AND table_name = '" & oTableName & "'"

                Dim connection As New SqlConnection(Globals.ThisAddIn.oRIGHT.lblConnectionString.Text)
                Dim cmd As New SqlCommand(sSQL, connection)
                connection.Open()
                Get_Primary_Key = cmd.ExecuteScalar().ToString
                connection.Close()
0
Comment
Question by:murbro
3 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40432381
A table can only have one primary key.

More than one column can be engaged in the primary key, but there will only be one primary key in the table.
0
 
LVL 33

Accepted Solution

by:
ste5an earned 250 total points
ID: 40432487
E.g.

 
USE AdventureWorks2012;

SELECT  KC.name ,
        C.name 
FROM    sys.schemas S
        INNER JOIN sys.tables T ON T.schema_id = S.schema_id
        INNER JOIN sys.key_constraints KC ON KC.parent_object_id = T.object_id
        INNER JOIN sys.index_columns IC ON IC.object_id = T.object_id
                                           AND IC.index_id = KC.unique_index_id
        INNER JOIN sys.columns C ON C.object_id = T.object_id
                                    AND C.column_id = IC.column_id
WHERE   S.name = 'Production'
        AND T.name = 'ProductInventory'
	AND KC.type = 'PK';

Open in new window

0
 

Author Closing Comment

by:murbro
ID: 40432524
Thanks for clarifying that a table can only one primary key. Also thanks for the code
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
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.

766 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