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:
>
>

Key Exists?

What's the best SQL statement to use to detect if a Key Exists in a
particular table?Try:
declare @.key_col ...
if exists(select * from t1 where key_col = @.key_col)
print 'exists'
else
print 'no exist'
go
AMB
"Les Stockton" wrote:

> What's the best SQL statement to use to detect if a Key Exists in a
> particular table?
>|||what do you mean?
that a key value exists?
SELECT * FROM yourtable WHERE key='value'
or that the table has a primary key?
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
TABLE_NAME = 'yourtable' and CONSTRAINT_TYPE = 'PRIMARY KEY')
print 'has primary key'
ELSE
print 'no primary key'
Les Stockton wrote:
> What's the best SQL statement to use to detect if a Key Exists in a
> particular table?
>|||More in knowing there is a primary key and is it named a certain name?
I have some code in VB that I inherited. It uses the SQLDMO to access the
database to do this:
For X = 1 To
SQLDMOConnection.Databases(UCase(DatabaseName)).Tables(TableName).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
What I want to do, is to not use the SQLDMO, but instead, using SQL directly
to see if a key by a certain name exists.
"Trey Walpole" wrote:

> what do you mean?
> that a key value exists?
> SELECT * FROM yourtable WHERE key='value'
> or that the table has a primary key?
> IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
> TABLE_NAME = 'yourtable' and CONSTRAINT_TYPE = 'PRIMARY KEY')
> print 'has primary key'
> ELSE
> print 'no primary key'
>
> Les Stockton wrote:
>|||In SQL-DMO, the key object has the name mapped to a contraint name. In t-SQL
the equivalent can be extracted using the metadata function OBJECTPROPERTY.
See the arguments, IsPrimaryKey and TableHasPrimaryKey in SQL Server Books
Online.
Anith