Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Which column is primary key ?

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-31 : 09:54:03
Stefan writes "Hello,

I want to find out wich column of my table is primary key with the help of system tables in each database. Also I want to find out wich column is identity (or it can't be update).

Thank you,
Stefan"

izaltsman
A custom title

1139 Posts

Posted - 2001-12-31 : 12:31:19
Primary key information can be obtained from the information schema views. You can either use them "as is" or reverse engineer to something along the lines of


select
i.name as PK_name
,t_obj.name as Table_name
,col.name as Column_name
from
sysobjects c_obj
inner join sysobjects t_obj ON c_obj.parent_obj = t_obj.id
inner join syscolumns col ON t_obj.id = col.id
cross join master.dbo.spt_values v
inner join sysindexes i
ON t_obj.id = i.id and c_obj.name = i.name
and col.name = index_col(t_obj.name,i.indid,v.number)
where
c_obj.xtype = 'PK'
and t_obj.xtype = 'U'
and v.number > 0
and v.type = 'P'
and v.number <= i.keycnt


As far as identity column goes, take a look at the syscolumns table, or try columnproperty function.



Edited by - izaltsman on 12/31/2001 12:32:25
Go to Top of Page

shyamprasad76
Starting Member

38 Posts

Posted - 2001-12-31 : 12:36:13
Hi,

Information schema views that are present in each and every database gives information about different things.

For example:

Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTARINT_TYPE = 'PRIMARY KEY' gives you all the columns in each nad every table in that database that are primary keys.

If you want to do this through ADO latest version
Here is the code:

set recordset = connection.OpenSchema(adSchemaFOreignKeys)

I am sure there will be one view to find the IDENTITY columns also.

Happy Coding,

Shyam.

Go to Top of Page
   

- Advertisement -