Re: find primary key with table name

On Jan 2, 4:47 am, "sybrandb" <sybra..._at_gmail.com> wrote:
> On Jan 2, 10:32 am, Jean-Luc M. <alphom..._at_free.fr> wrote:
>
>
>
>
>
> > 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.alter table <table_name> drop primary key
>
> No need to search the name.
>
> Oracle != sqlserver
>
> NEVER EVER try to port sqlserver code to PL/SQL. It won't work and it
> won't scale.
> Please ALWAYS read the documentation PRIOR to asking mere doc
> questions!!!
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -- Show quoted text -

If you need to know if a table has a PK, UK, or FK constraint defined on it or to it as the case may be you can look at the rdbms dictionary view DBA_CONSTRAINTS. You can find the rdbms dictionary views defined in the Oracle version# Reference manual. The manuals are available online if you do not have a copy of the documentation CD handy.

But just to drop it. as Sybrand pointed out, you do not even need to know the PK constraint name.

HTH -- Mark D Powell -- Received on Tue Jan 02 2007 - 08:29:00 CST