How to determine actual constraint name by passing a column name

I need a query in which I can pass in a column name and table name and will
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
select db_name() as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid) as OWNER
,c_obj.name as CONSTRAINT_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,c_obj.xtype as XTYPE
from
sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype = 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
RachelDid you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Yes, but that does not provide information on Unique Constraints ( UQ)
"Tibor Karaszi" wrote:
> Did you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> >I need a query in which I can pass in a column name and table name and will
> > be provided all constraints ( all types ) for that given column in that
> > table. I've created and found several queries that work for various types
> > but have not found one that does what I need for Unique constraints. I have
> > the following for FKs, but it doesn't work for UQs
> > select db_name() as DATABASE_name
> > ,t_obj.name as TABLE_NAME
> > ,user_name(c_obj.uid) as OWNER
> > ,c_obj.name as CONSTRAINT_NAME
> > ,col.name as COLUMN_NAME
> > ,col.colid as ORDINAL_POSITION
> > ,c_obj.xtype as XTYPE
> > from
> > sysobjects c_obj
> > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > join sysconstraints con on c_obj.id = con.constid
> > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > where
> > c_obj.xtype = 'F'
> > ORDER BY t_obj.name
> >
> > If anyone can point out what I am missing, I'd really appreciate it.
> > Thanks
> > Rachel
>
>|||> Yes, but that does not provide information on Unique Constraints ( UQ)
Really? Try this repro, and let us know how it works out for you.
USE tempdb
GO
CREATE TABLE dbo.foobar
(
foo INT NOT NULL UNIQUE,
bar VARCHAR(12)
)
ALTER TABLE dbo.foobar ADD CONSTRAINT UQ_Bar UNIQUE(bar)
GO
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME='foobar' AND COLUMN_NAME IN ('foo','bar')
GO
DROP TABLE dbo.foobar
GO
http://www.aspfaq.com/
(Reverse address to reply.)|||You need to do a little more work, but the starting point is the view
mentioned by Tibor. Logically, you need to determine if the column is
associated with a constraint and if the associated constraint is a unique
constraint. Constraint information can be found in the TABLE_CONSTRAINTS
view. hint - looks like a join is involved. BTW - what if unique-ness is
enforced with an index and not a constraint?
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:7CD67018-4028-4823-8320-26D90DB84B62@.microsoft.com...
> Yes, but that does not provide information on Unique Constraints ( UQ)
> "Tibor Karaszi" wrote:
> > Did you try the INFORMATION_SCHEMA views? For instance
CONSTRAINT_COLUMN_USAGE.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> > news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> > >I need a query in which I can pass in a column name and table name and
will
> > > be provided all constraints ( all types ) for that given column in
that
> > > table. I've created and found several queries that work for various
types
> > > but have not found one that does what I need for Unique constraints.
I have
> > > the following for FKs, but it doesn't work for UQs
> > > select db_name() as DATABASE_name
> > > ,t_obj.name as TABLE_NAME
> > > ,user_name(c_obj.uid) as OWNER
> > > ,c_obj.name as CONSTRAINT_NAME
> > > ,col.name as COLUMN_NAME
> > > ,col.colid as ORDINAL_POSITION
> > > ,c_obj.xtype as XTYPE
> > > from
> > > sysobjects c_obj
> > > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > > join sysconstraints con on c_obj.id = con.constid
> > > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > > where
> > > c_obj.xtype = 'F'
> > > ORDER BY t_obj.name
> > >
> > > If anyone can point out what I am missing, I'd really appreciate it.
> > > Thanks
> > > Rachel
> >
> >
> >|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> > LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> > I need a query in which I can pass in a column name and table name and
> will
> > be provided all constraints ( all types ) for that given column in that
> > table. I've created and found several queries that work for various
types
> > but have not found one that does what I need for Unique constraints. I
> have
> > the following for FKs, but it doesn't work for UQs
> > select db_name() as DATABASE_name
> > ,t_obj.name as TABLE_NAME
> > ,user_name(c_obj.uid) as OWNER
> > ,c_obj.name as CONSTRAINT_NAME
> > ,col.name as COLUMN_NAME
> > ,col.colid as ORDINAL_POSITION
> > ,c_obj.xtype as XTYPE
> > from
> > sysobjects c_obj
> > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > join sysconstraints con on c_obj.id = con.constid
> > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > where
> > c_obj.xtype = 'F'
> > ORDER BY t_obj.name
> >
> > If anyone can point out what I am missing, I'd really appreciate it.
> > Thanks
> > Rachel
>