Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how get primary key in any database Expand / Collapse
Author
Message
Posted Thursday, January 22, 2009 7:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 22, 2010 8:43 AM
Points: 99, Visits: 994
Comments posted to this topic are about the item how get primary key in any database
Post #641675
Posted Thursday, January 29, 2009 5:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:23 AM
Points: 242, Visits: 939
This information can be gotten despite how the constraint is named this way:

SELECT Table_name, constraint_name 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type = 'PRIMARY KEY'

then you can use the table_name and constraint_name to do what you wish.

Toni
Post #645769
Posted Thursday, January 29, 2009 5:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:23 AM
Points: 242, Visits: 939
To be clearer, as you can name the primary key anything you want the statements from the article would miss any that do not begin with 'PK'.
declare @name_pk varchar(255)

select @name_pk = name from sysindexes where id in (select id from sysobjects where name = 'table_name') and name like 'PK%'

execute ('alter table table_name drop constraint ' + @name_pk)

So to get the name of the Primary Key Constraint without this restriction, using the same 'table_name' substitution:
declare @name_pk varchar(255)

SELECT @name_pk = constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE constraint_type = 'PRIMARY KEY' and Table_name = 'table_name'

Print 'pk ' + @name_pk -- show the results

/* or use the execute as in the original */
-- execute ('alter table table_name drop constraint ' + @name_pk)

Toni
Post #645778
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse