Key Exists? (how to test) - SQL Server Forums

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

Transact-SQL (2000)

Key Exists? (how to test)

Author

Topic

HockeyFan
Starting Member

26 Posts

Posted - 2005-12-14 : 11:15:55

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

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?

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'),'TableHasPrimaryKey')

Now those are elements of what I need.
What are the full statements to make it work?

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 11:40:30

Have a look at

SELECT TOP 100 * FROM INFORMATION_SCHEMA.TABLES
and
SELECT TOP 100 * FROM INFORMATION_SCHEMA.COLUMNS

? Or is that overly simplistic?

Kristen

HockeyFan
Starting Member

26 Posts

Posted - 2005-12-14 : 11:46:29

Well, I'm somewhat new to SQL and learning parts but others aren't so easy for me, and so I was hoping that with what I have so far, someone could construct a SQL statement that would work.

HockeyFan
Starting Member

26 Posts

Posted - 2005-12-14 : 11:55:26

I tried looking at the schema, but I didn't see anything there that tells if a primary key exists with a particular name.
I couldn't see that anything was there that said a field was a primary key or foreign key.

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 12:20:06

Yeah, there are "yet more" tables that have that sort of information - like INFORMATION_SCHEMA.KEY_COLUMN_USAGE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51211

and perhaps also:

http://www.google.com/search?q=site%3Asqlteam.com+INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Kristen

HockeyFan
Starting Member

26 Posts

Posted - 2005-12-14 : 12:27:37

And this will work cross versions of SQLServer?

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 12:55:49

Relatively recent innovation (if you can call it that!)

Perhaps have a look on www.mindsdoor.net for the DMO stuff (down the bottom of the page) if you want to stick with the DMO route.

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-14 : 13:09:17

>>What's the best SQL statement to use to detect if a Key Exists in a
particular table?

You might also want to check out sp_pkeys and sp_fkeys

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources