Friday, March 9, 2012

How to get Primary Key (Columns) of a Table?

I want to get the Primary Key Columns in Arrays by sending a table
name. I am using SQL Server 2000 and I want to make a find utility in VB.net which
will work for all the forms; I have tables with one Primary key and some tables with composite Primary keys.

I used to do this in VB 6 by making a function which fills the Primary Keys in
List Box (I require to fill in list box), now I need to get in array.

Can some one tell me the migration of the following VB 6 Code?

This was written for the MS Access, I need same for SQL Server, I can
not find Table Def and Index Object in VB.net 2003.

Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName As
String)

Dim lttabDef As TableDef
Dim liCounter As Integer
Dim liLoop As Integer
Dim idxLoop As Index
Dim fldLoop As Field

With ldbDatabase
For Each lttabDef In .TableDefs
If lttabDef.Name = lsTableName Then
liCounter = lttabDef.Fields.Count
For liLoop = 0 To liCounter - 1
cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name
Next liLoop
For Each idxLoop In lttabDef.Indexes
With idxLoop
lblIndexName = .Name
If .Primary Then
liCounter = 0
For Each fldLoop In .Fields
cboPrimaryKeys.List(liCounter) = fldLoop.Name
liCounter = liCounter + 1
Next fldLoop
End If
End With
Next
cboFieldLists.ListIndex = 0
If cboPrimaryKeys.ListCount > 0 Then
cboPrimaryKeys.ListIndex = 0
End If
Exit For
End If
Next
End With
End Sub

SQL Server has a built-in system stored procedure calledsp_primarykeys whichwill return you the primary key information for the table you specify.
USE master
EXEC sp_primarykeys @.table_server = N'LONDON1',
@.table_name = N'Customers',
@.table_catalog = N'Northwind',
@.table_schema = N'dbo'
|||Thanks TmortonSmile [:)]

No comments:

Post a Comment