Solved

VB.net SQL Check if Primary key is an identity column

Posted on 2013-06-25
5
469 Views
Last Modified: 2013-06-25
Hi

I use the following VB.net code to check if a column is a primary key.
How do I find if it is an identity column
For example "[ID] bigint identity(1,1) primary key" is an identity column
and "[ID] bigint primary key" is not an identity column


               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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 22

Assisted Solution

by:Om Prakash
Om Prakash earned 167 total points
ID: 39274091
Try:
SELECT name, is_identity FROM sys.columns WHERE [object_id] = object_id('table_name')

Open in new window

which will list column name with is_identity column value as 1 if the column has identity
0
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 166 total points
ID: 39274094
to check if column is mandatory, u simply check the column.AllowDBNull property:

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.allowdbnull.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2
0
 
LVL 13

Accepted Solution

by:
Philip Pinnell earned 167 total points
ID: 39274096
You probably want something like

SELECT [IS_NULLABLE]
FROM [msdb].[INFORMATION_SCHEMA].[COLUMNS]
where [column_name] = 'myColumn'
0
 

Author Closing Comment

by:murbro
ID: 39274109
Thanks very much
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 39274243
Thanks
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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ā€¦
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

736 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