- to get PK Name --
SELECT CONSTRAINT_NAME
INTO PK_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = <owner>
AND TABLE_NAME = <table name>
AND CONSTRAINT_TYPE = 'P'
- to get the PK columns --
SELECT B.COLUMN_NAME,A.CONSTRAINT_NAME
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B
WHERE A.OWNER = <owner>
AND A.TABLE_NAME = <table name>
AND A.CONSTRAINT_TYPE = 'P'
AND B.OWNER = A.OWNER
AND B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
ORDER BY B.POSITION
Steve
"Jean-Luc M." <alphomega_at_free.fr> wrote in message news:mn.12787d71e3110dcb.28556_at_free.fr...
> Hi,
>
> I need to translate the T-SQL code :
>
> SELECT
> @PK_NAME = so.name
> FROM
> sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
> WHERE
> object_name(so.parent_obj) = @TABLE AND so.xtype = 'PK'
>
> -- Si on a trouvé la PK on la supprime
> IF @PK_NAME IS NOT NULL
> EXECUTE('alter table [' + @TABLE + '] DROP CONSTRAINT ' + @PK_NAME)
> ;
>
> in PL-SQL
>
> How to find the primary key for a table name with PL-SQL and delete it
> ?
>
> Thanks and happy new year !
>
> Jean-Luc !
>
> --
> Jean-Luc M.
>
>
Received on Thu Jan 04 2007 - 19:01:38 CST
