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
selectdb_name()as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid)as OWNER
,c_obj.nameas CONSTRAINT_NAME
,col.nameas COLUMN_NAME
,col.colidas ORDINAL_POSITION
,c_obj.xtypeas XTYPE
from
sysobjectsc_obj
join sysobjectst_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id= con.constid
join syscolumnscol 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
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:
> 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[vbcol=seagreen]
> 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...
> will
types
> have
>
sql