Key Exists?

What's the best SQL statement to use to detect if a Key Exists in a
particular table?
I had been using SQLDMO within a VB app to access possible keys in the table
and then find if one matches what I'm looking for:
For X = 1 To SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableNam
e)
.Keys.Count
If Trim(UCase(KeyName)) = UCase(Trim(SQLDMOConnection.Databases(UCase
(DatabaseName)).Tables(TableName).Keys(X).Name)) Then
KeyExists = True
Exit For
End If
Next X
I've decided not to do this, and instead use SQL statements to get the
information.
So I need some way of traversing keys on a table and see the names and find
a
match to thename I'm looking for.
How's the best way to do this?Okay. I've got some of what I need.
I know that I can use OBJECTPROPERTY(OBJECT_ID('tablename.fieldname'),
'IsPrimaryKey') to find out if a field is a key. Can I specify table/field
in the OBJECT_ID call?
Also, before I do this, I'd like to check the table to see if it has a
primary key.
So...
OBJECTPROPERTY(OBJECT_ID('tablename'),'T
ableHasPrimaryKey')
Now those are elements of what I need.
What are the full statements to make it work?
E. coli Happens.|||It would sure be nice if someone could take the pieces and put them together
into a sql statement or statements that I can use.
Les Stockton wrote:
>Okay. I've got some of what I need.
>I know that I can use OBJECTPROPERTY(OBJECT_ID('tablename.fieldname'),
>'IsPrimaryKey') to find out if a field is a key. Can I specify table/field
>in the OBJECT_ID call?
>Also, before I do this, I'd like to check the table to see if it has a
>primary key.
>So...
> OBJECTPROPERTY(OBJECT_ID('tablename'),'T
ableHasPrimaryKey')
>Now those are elements of what I need.
>What are the full statements to make it work?
>
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||This would list all the tables in the current database that have a primary
key, and the name of the primary key on the table.
SELECT s1.[name] AS "Table", s2.[name] AS "Key"
FROM sysobjects s1 INNER JOIN sysobjects s2
ON s2.[parent_obj]=s1.[id]
WHERE s2.[xtype]='PK'
You could add a WHERE clause to look at a specific table and an aggregate
COUNT to get a 0 or 1 returned from the statement.
SELECT COUNT(*)
FROM sysobjects s1 INNER JOIN sysobjects s2
ON s2.[parent_obj]=s1.[id]
WHERE s2.[xtype]='PK' AND s1.[name]='table_name'
Returns 1 if table_name has a primary key, and zero if it doesn't.
"HockeyFan" wrote:

> What's the best SQL statement to use to detect if a Key Exists in a
> particular table?
> I had been using SQLDMO within a VB app to access possible keys in the tab
le
> and then find if one matches what I'm looking for:
> For X = 1 To SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableN
ame)
> ..Keys.Count
> If Trim(UCase(KeyName)) = UCase(Trim(SQLDMOConnection.Databases(UCase
> (DatabaseName)).Tables(TableName).Keys(X).Name)) Then
> KeyExists = True
> Exit For
> End If
> Next X
> I've decided not to do this, and instead use SQL statements to get the
> information.
> So I need some way of traversing keys on a table and see the names and fin
d a
> match to thename I'm looking for.
> How's the best way to do this?
>|||I did.
How much of my post did you read?
Les Stockton via webservertalk.com wrote:
> It would sure be nice if someone could take the pieces and put them togeth
er
> into a sql statement or statements that I can use.
> Les Stockton wrote:
>
>