How to find primary key constraint name?

How to find primary key constraint name?

Post by Jesse Fittere » Sun, 05 Jan 2003 04:16:33



How do I find the name of a constraint?  I want to set the name of the
primary key constraint name to a variable so I can drop the constraint
to add a new constraint. Currently the system name is used as a
constraint as it was not defined.  I used the sp_help to find out but I
need just the name to set it to a variable.  ANy help please? thx

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

How to find primary key constraint name?

Post by Jesse Fittere » Sun, 05 Jan 2003 04:36:33


Thanks for the reply.  I have used the sp_help but it returns
everything.  I just need the index_name so I can set that value to a
variable.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

  _alt.0
5K Download

 
 
 

How to find primary key constraint name?

Post by Anith Se » Sun, 05 Jan 2003 04:19:03


Use sp_help or sp_pkeys. Another option is to query the relevant
ANSI views or system tables. See details on SQL Server Books Online.

--
- Anith
(Please respond only to newsgroups)

 
 
 

How to find primary key constraint name?

Post by Anith Se » Sun, 05 Jan 2003 04:44:07


Try:



                    FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
                   WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME),
                                                  'IsPrimaryKey') = 1
                     AND TABLE_NAME = 'authors')

--
- Anith
(Please respond only to newsgroups)

 
 
 

How to find primary key constraint name?

Post by John Bel » Sun, 05 Jan 2003 05:01:51


Hi

You may wish to query the information_schema.table_constraints view if you
have sql 7 or later.

John


Quote:> How do I find the name of a constraint?  I want to set the name of the
> primary key constraint name to a variable so I can drop the constraint
> to add a new constraint. Currently the system name is used as a
> constraint as it was not defined.  I used the sp_help to find out but I
> need just the name to set it to a variable.  ANy help please? thx

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

How to find primary key constraint name?

Post by Jesse Fittere » Sun, 05 Jan 2003 05:04:52


Thanks, this is exactly what I was looking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. tempdb naming scope for Primary Key constraints *global* on local #temptables

Hello

I wish to report and share very counter-intuitive and IMHO un-desirable
behavior in SQL6.5 and SQL7.0. Perhaps someone "in the know" can explain the
reasoning behind this 'feature'.

I know this is not a bug but nevertheless a very inconvenient feature as far
as I can see:

please consider the following T-SQL code snip
------------------
        create table #InParamsDate
        (
        CompanyCode     char(12) not null default '',
        PeriodEndDate   datetime not null default '1 Jan 1800 12:00am'
        )
        alter table #InParamsDate
            add constraint TT_InParamsDate__PK primary key nonclustered
(CompanyCode,PeriodEndDate)
-----------------------
Note the primary key constraint name is TT_InParamsDate__PK ---- for future
reference.

If you now go and execute this code in one QueryAnalyser session, then all
will be well. Now go and execute the same code in a second session (leave
the first one running) and you will get the following error:

Server: Msg 2714, Level 16, State 4, Line 7
There is already an object named 'TT_InParamsDate__PK' in the database.
Server: Msg 1750, Level 16, State 1, Line 7
Could not create constraint. See previous errors.

----

This occurs because the name of the primary key *constraint* is *global* to
the tempdb even though it is hanging off the *session local*  temporary
table. I find this very counter-intuitive and un-desirable behavior and
would like to know why it works this way. We now have to change chunks of
code to generate names for constraints dynamically and then use dynamic SQL
8^(

Perhaps this behavior could be improved in a near future version of SQL
Server. I tried it on SQL7.0 SP1 beta and it behaves the same way as 6.5 and
7.0 RTM....

Looking for some enlightenment

thanks in advance
--
Yuri Budilov (always expressing personal opinions only)
Melbourne, Australia

2. Delphi 3(SR2) & Access97

3. Primary key constraints vs Unique constraints

4. UDF Input Datatype question

5. finding the column name of a Primary Key

6. How to understand this query?

7. Finding the Primary Key Column name programatically?

8. Finding the field name of the primary key

9. Changing Primary Key Values when foreign key constraints exist

10. BCP INTO A TABLE THAT CONTAINS PRIMARY KEY AND FOREIGN KEY CONSTRAINTS

11. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object