Hi all
Would there be a easy way to find the column name(s) which constitute
a Primary constraint for a table through navigating the system
catalogs.
I found that the PK Constraint object in syscontraints is showing the
colid = 0.
TIA
Norman 2 5984
"Norman Leung" <no*****@interlog.com> wrote in message
news:3d**************************@posting.google.c om... Hi all
Would there be a easy way to find the column name(s) which constitute a Primary constraint for a table through navigating the system catalogs.
I found that the PK Constraint object in syscontraints is showing the colid = 0.
colid = 0 indicates that it's a composite PK.
To find the columns of the PK, just use the system stored procedure
"sp_pkeys", for example,
sp_pkeys sometablename
However, if you insist on using the system tables to do this, have a look at
the source code for the sp_pkeys procedure. Apparently, you need to join
the syscolumns table with the sysindexes table, filter with a 0x800 mask (I
assume this selects for a primary key index), then select for columns based
on column name using the procedure index_col().
- Dave
....
from
sysindexes i, syscolumns c, sysobjects o
where
o.id = @table_id
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@full_table_name, i.indid, c1.colid)
and (c.name = index_col (@full_table_name, i.indid, 1) or
c.name = index_col (@full_table_name, i.indid, 2) or
c.name = index_col (@full_table_name, i.indid, 3) or
c.name = index_col (@full_table_name, i.indid, 4) or
c.name = index_col (@full_table_name, i.indid, 5) or
c.name = index_col (@full_table_name, i.indid, 6) or
c.name = index_col (@full_table_name, i.indid, 7) or
c.name = index_col (@full_table_name, i.indid, 8) or
c.name = index_col (@full_table_name, i.indid, 9) or
c.name = index_col (@full_table_name, i.indid, 10) or
c.name = index_col (@full_table_name, i.indid, 11) or
c.name = index_col (@full_table_name, i.indid, 12) or
c.name = index_col (@full_table_name, i.indid, 13) or
c.name = index_col (@full_table_name, i.indid, 14) or
c.name = index_col (@full_table_name, i.indid, 15) or
c.name = index_col (@full_table_name, i.indid, 16)
) TIA Norman no*****@interlog.com (Norman Leung) wrote in message news:<3d**************************@posting.google. com>... Hi all
Would there be a easy way to find the column name(s) which constitute a Primary constraint for a table through navigating the system catalogs.
I found that the PK Constraint object in syscontraints is showing the colid = 0.
TIA Norman
SELECT
KCU.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON KCU.TABLE_NAME = CCU.TABLE_NAME AND
KCU.COLUMN_NAME = CCU.COLUMN_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
KCU.TABLE_NAME = 'MyTable' AND
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
Simon This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: me |
last post by:
I would like to add an Identity to an existing column in a table using a
stored procedure then add records to the table and then remove the identity
after the records have been added or something...
|
by: adammitchell |
last post by:
How can you indicate that a FOREIGN KEY constraint references two
columns in two different tables?
"SQL Server Books Online" show an example of how to reference two
columns in the SAME table:...
|
by: christopher.secord |
last post by:
Is there any advantage to doing this:
ALTER TABLE testtable ADD
CONSTRAINT PK_sysUser
PRIMARY KEY NONCLUSTERED (UserID)
WITH FILLFACTOR = 100,
CONSTRAINT IX_sysUser
UNIQUE NONCLUSTERED...
|
by: Joe |
last post by:
Hi All,
I am new to using the Access DB and I need some help if someone
is able to give it to me. What I want to do is get the names of
the columns of certain tables. Not the data in the table...
|
by: RamaKrishna Narla |
last post by:
In MS SQL Server, I have the following tables with some data in it.
create table table1 (
column1 varchar(32),
column2 int not null,
column10 varchar(255),
.....
primary key (column1,...
|
by: sameer_deshpande |
last post by:
Hi,
I need to create a partition table but the column on which I need to
create a partition may not have any logical ranges. So while creating
or defining partition function I can not use any...
|
by: =?Utf-8?B?QmFidU1hbg==?= |
last post by:
Hi,
I have a GridView and a SqlDataSource controls on a page. The SqlDataSource
object uses stored procedures to do the CRUD operations. The DataSource has
three columns one of which -...
|
by: Jeff Kish |
last post by:
Hi.
I've read up on this, and have something that works, but I was wondering if
there is anything I'm overlooking with this.
Situation is:
I have a bunch of tables.. I need to modify table2...
|
by: Shestine |
last post by:
I am trying to add a column to a current table, with data in it. I am only learning, and i have no idea how to change this to make it work. Here is the script I have right now it, but what it does is...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |