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