Find names of all constraints on column

Find names of all constraints on column

Post by Jim Elde » Thu, 20 Dec 2001 04:04:30



How do I SELECT the names of all constraints on one column,
so that I can delete them prior to dropping the column?

My problem is that the column has a default constraint on it with
a system-assigned name and I would like to script the DROP
COLUMN without knowing the name of the constraint in advance.

Thanks!
Jim

 
 
 

Find names of all constraints on column

Post by Dinesh T » Thu, 20 Dec 2001 04:25:36


Jim,

EXEC sp_helpconstraint <tablename>

To be specific, refer the information_schema.* views like

select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA.KEY_COLUMN_USAGE etc.

Dinesh.


> How do I SELECT the names of all constraints on one column,
> so that I can delete them prior to dropping the column?

> My problem is that the column has a default constraint on it with
> a system-assigned name and I would like to script the DROP
> COLUMN without knowing the name of the constraint in advance.

> Thanks!
> Jim


 
 
 

Find names of all constraints on column

Post by Jim Elde » Thu, 20 Dec 2001 04:42:31


Dinesh,

Thanks for such a quick response.   Can I do the equivalent of this...



 object_name(c.cdefault)
 from syscolumns c,syscomments m
 where c.id = object_id( 'MyTable' )
   and c.name = 'my_column_name'
   and m.id = c.cdefault
   and ObjectProperty(c.cdefault, 'IsConstraint') = 1


go

...by using INFORMATION_SCHEMA instead of syscolumns and syscomments?

I'd rather not use the system tables directly.

Thanks,
Jim

 
 
 

Find names of all constraints on column

Post by Dinesh T » Thu, 20 Dec 2001 06:03:45


Jim,

I doubt it since I cant find the default constraint name stored in any of
those views.

But it sure is returned by sp_helpconstraint <tablename>
Dinesh.


> Dinesh,

> Thanks for such a quick response.   Can I do the equivalent of this...



>  object_name(c.cdefault)
>  from syscolumns c,syscomments m
>  where c.id = object_id( 'MyTable' )
>    and c.name = 'my_column_name'
>    and m.id = c.cdefault
>    and ObjectProperty(c.cdefault, 'IsConstraint') = 1


> go

> ...by using INFORMATION_SCHEMA instead of syscolumns and syscomments?

> I'd rather not use the system tables directly.

> Thanks,
> Jim

 
 
 

Find names of all constraints on column

Post by Tibor Karasz » Thu, 20 Dec 2001 20:19:20


Quote:> I doubt it since I cant find the default constraint name stored in any of
> those views.

FWIW (in case you don't know it)...
A default is in ANSI SQL a column attribute, where in SQL Server it is a constraint. Since the
structure of the INFORMATION_SCHEMA views are defined by ANSI, there's no place for default
constraints in those views...

Also, NOT NULL is essentially a constraint in ANSI, where it is a column attribute in SQL
Server. The other way around... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Jim,

> I doubt it since I cant find the default constraint name stored in any of
> those views.

> But it sure is returned by sp_helpconstraint <tablename>
> Dinesh.


> > Dinesh,

> > Thanks for such a quick response.   Can I do the equivalent of this...



> >  object_name(c.cdefault)
> >  from syscolumns c,syscomments m
> >  where c.id = object_id( 'MyTable' )
> >    and c.name = 'my_column_name'
> >    and m.id = c.cdefault
> >    and ObjectProperty(c.cdefault, 'IsConstraint') = 1


> > go

> > ...by using INFORMATION_SCHEMA instead of syscolumns and syscomments?

> > I'd rather not use the system tables directly.

> > Thanks,
> > Jim

 
 
 

Find names of all constraints on column

Post by Dinesh T » Thu, 20 Dec 2001 22:05:54


Thanks Tibor... I was unaware abt that.
Now it makes sense :)

Dinesh.



Quote:> > I doubt it since I cant find the default constraint name stored in any
of
> > those views.

> FWIW (in case you don't know it)...
> A default is in ANSI SQL a column attribute, where in SQL Server it is a

constraint. Since the
Quote:> structure of the INFORMATION_SCHEMA views are defined by ANSI, there's no
place for default
> constraints in those views...

> Also, NOT NULL is essentially a constraint in ANSI, where it is a column
attribute in SQL
> Server. The other way around... :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




> > Jim,

> > I doubt it since I cant find the default constraint name stored in any
of
> > those views.

> > But it sure is returned by sp_helpconstraint <tablename>
> > Dinesh.


> > > Dinesh,

> > > Thanks for such a quick response.   Can I do the equivalent of this...



> > >  object_name(c.cdefault)
> > >  from syscolumns c,syscomments m
> > >  where c.id = object_id( 'MyTable' )
> > >    and c.name = 'my_column_name'
> > >    and m.id = c.cdefault
> > >    and ObjectProperty(c.cdefault, 'IsConstraint') = 1

> > >  exec( 'ALTER TABLE MyTable DROP CONSTRAINT ' +


- Show quoted text -

Quote:> > > go

> > > ...by using INFORMATION_SCHEMA instead of syscolumns and syscomments?

> > > I'd rather not use the system tables directly.

> > > Thanks,
> > > Jim

 
 
 

Find names of all constraints on column

Post by BP Margoli » Fri, 21 Dec 2001 09:43:18


Tibor,

Pretty soon, Joe Celko is going to be asking YOU questions about the ANSI
SQL standard    :-)

BPM



Quote:> > I doubt it since I cant find the default constraint name stored in any
of
> > those views.

> FWIW (in case you don't know it)...
> A default is in ANSI SQL a column attribute, where in SQL Server it is a

constraint. Since the
Quote:> structure of the INFORMATION_SCHEMA views are defined by ANSI, there's no
place for default
> constraints in those views...

> Also, NOT NULL is essentially a constraint in ANSI, where it is a column
attribute in SQL
> Server. The other way around... :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




> > Jim,

> > I doubt it since I cant find the default constraint name stored in any
of
> > those views.

> > But it sure is returned by sp_helpconstraint <tablename>
> > Dinesh.


> > > Dinesh,

> > > Thanks for such a quick response.   Can I do the equivalent of this...



> > >  object_name(c.cdefault)
> > >  from syscolumns c,syscomments m
> > >  where c.id = object_id( 'MyTable' )
> > >    and c.name = 'my_column_name'
> > >    and m.id = c.cdefault
> > >    and ObjectProperty(c.cdefault, 'IsConstraint') = 1

> > >  exec( 'ALTER TABLE MyTable DROP CONSTRAINT ' +


- Show quoted text -

Quote:> > > go

> > > ...by using INFORMATION_SCHEMA instead of syscolumns and syscomments?

> > > I'd rather not use the system tables directly.

> > > Thanks,
> > > Jim

 
 
 

Find names of all constraints on column

Post by Jim Elde » Fri, 21 Dec 2001 22:07:29


ROTFLMAO!


Quote:> Tibor,

> Pretty soon, Joe Celko is going to be asking YOU questions about the ANSI
> SQL standard    :-)

> BPM

 
 
 

Find names of all constraints on column

Post by Tibor Karasz » Sat, 22 Dec 2001 21:55:05


BP,

Quote:> Pretty soon, Joe Celko is going to be asking YOU questions about the ANSI
> SQL standard    :-)

Hehe. I doubt it, I'm not very good at deciphering the text in the ANSI doc's. :-)
I wonder if it pays, the job to "scramble" text into something which is almost unreadable. (On a
serious note, I do understand that this is how it gets in order to have precise expressions...).

Perhaps SQL:1999, though. I guess that we all know more than Joe on that, as he more or less
refuses to acknowledge the existence of SQL:1999. ;-)
(Yes, Joe, I have seen some of your objections on SQL.1999. I just think that this is what we
have and evolution will take us there, sooner or later...)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Tibor,

> Pretty soon, Joe Celko is going to be asking YOU questions about the ANSI
> SQL standard    :-)

> BPM

 
 
 

1. Problem in retriving in column name using constraint name

Hello,

I got a problem in retriving information from system catalog.
I created  the following table in the database

CREATE TABLE Address
(
    number               INTEGER             NOT NULL,
    name                 CHAR(35)            NOT NULL,
    vorname              CHAR(35)            NOT NULL,
    children             DECIMAL(22)         NOT NULL,
    UNIQUE (number),
    UNIQUE (name)
)  LOCK MODE ROW;

and i delebarately entered two rows with same names . The sqlca structure
has returned the following information

     result last sqlstat/stat type (sqlca.sqlcode):            -268 dec
     informix error message        (sqlca.sqlerrm):     >>voora.u102_8<<
     esitmated nr of rows returned (sqlca.sqlerrd[0]):         1 dec
     serial value or ISAM err code (sqlca.sqlerrd[1]):         0 dec
     number or rows processed      (sqlca.sqlerrd[2]):         0 dec
     estimated cost                (sqlca.sqlerrd[3]):         1 dec
     offset of error in statement  (sqlca.sqlerrd[4]):        73 dec
     rowid of last row processed   (sqlca.sqlerrd[5]):         0 dec

The system has assigned the names to the Unique constraints
__________________________________
Constraint Name    Column Name
__________________________________
u102_7             number
u102_8             name
___________________________________
My problem is i want to retrive the column name(ie name) using Constraint Name(u102_8)
using an SQL statement from system catalogs.

Please provide me the relating info.

Regards
voora srinivas

2. ?? ER Diagram Software ??

3. Constraint name constraint-name already exists.

4. HELP! HELP! Memo and UNION!!!

5. HELP: Finding index names in sysindexes using a column name

6. Enter username and password for sql server onload of A2K

7. ADP -- Can't Find column (column name)

8. size of rollback write

9. How to find primary key constraint name?

10. Find Default Constraint Names !

11. Find Default Constraint Names

12. Find Default Constraint names

13. Need to find out the name of the unique constraint