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 2005 Forums
 Transact-SQL (2005)
 Check if a primary keys exists

Author  Topic 

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-04 : 11:18:03
Hello all.

OK i have script which is to be run on several databases. Within this script there are commands to create a primary key on a specific table. Can anyone tell me if it is possible to check if a specific primary key exists on a table?

Thanks people.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-04 : 11:24:52
Use ObjectProperty() function with TableHasPrimaryKey argument.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-04 : 12:32:23
Hi thanks for the reply Harsh. In the end i opted for the following:

quote:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME = '[tblReturnLine]'
AND TABLE_SCHEMA ='dbo' )
BEGIN
ALTER TABLE [dbo].[tblReturnLine] ADD CONSTRAINT [PK_tblReturnLine] PRIMARY KEY CLUSTERED ([ID])
PRINT N'Creating primary key [PK_tblReturnLine] on [dbo].[tblReturnLine]'
END


However this doesnt seem to be working as required. The first time i run it is fine, no primary key exists and it correctly creates it. However the second time i run it........it doesnt seem to be picking up the fact a primary key exists and attempts to create a new one causing an error. Any thoughts?

Thanks again.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-04 : 12:42:07
You have INFORMATION_SCHEMA views to query too.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

dkreimer
Starting Member

1 Post

Posted - 2014-04-22 : 21:57:41
I believe I see your problem. In the second line of your quote, you have:
AND TABLE_NAME = '[tblReturnLine]'

I believe that this should be:
AND TABLE_NAME = 'tblReturnLine'
(i.e., no brackets.)

Cheers,

David Kreimer
Go to Top of Page
   

- Advertisement -