Finding Constraint Column Names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Finding Constraint Column Names

Author

Topic

phil_mw60
Starting Member

4 Posts

Posted - 2010-03-29 : 09:33:22

Hi,

I have the following query which returns a list of constraints for the current database and the table name to which the constraint applies:

select dc.name as [Constraint Name], dc.definition, so.name as [Table]
from sys.default_constraints dc
left join dbo.sysobjects so
on dc.parent_object_id = so.id
and so.xtype = 'u'
order by so.name

I would like to extend this query to show me the column that the constraint applies to.

I have tried to join onto the syscolumns table but this returns numerous rows for each constraint.

Does anyone have any ideas how to extend this query to show the column name?

Thanks in advance,

Phil

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-29 : 12:51:58

[code]select t.name [table], c.name [column], d.name [constraint]
from sys.default_constraints d
join sys.tables t
on t.object_id = d.parent_object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 12:57:59

I prefer using INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE for this

http://msdn.microsoft.com/en-us/library/ms174431.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

phil_mw60
Starting Member

4 Posts

Posted - 2010-03-30 : 06:05:47

Hi,

Thanks for your answers.

I'm sure the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE is probably easier but I have gone with Russell's solution of joining on the sys.columns table..

I think I was almost there but didn't specify the join between sys.columns and sys.tables hence I was getting numerous rows returned!

Thanks very much.

phil_mw60
Starting Member

4 Posts

Posted - 2010-04-08 : 08:34:18

ps:

Does anyone know of a SQL Server 2000 equivalent query to return details of table / column constraints for a given Database?

I tried running:

select t.name as [Table], dc.name as [Constraint Name], dc.definition, c.name as [ColumnName]
from sys.default_constraints dc
inner join sys.tables t
on t.object_id = dc.parent_object_id
inner join sys.columns c
on c.object_id = t.object_id
and c.column_id = dc.parent_column_id
order by t.name, c.name

against a SQL 2000 instance but this fails stating invalid object names for sys.*

Thanks!

Phil

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-04-08 : 10:06:05

[code]
select o.name [table], c.name [column], object_name(d.constid) [constraint]
from sysconstraints d
join sysobjects o
on o.id = d.id
join syscolumns c
on c.id = o.id
and c.colid = d.colid
where d.status & 5 = 5
and o.xtype = 'U'[/code]

have a look at these:
sysconstraints http://msdn.microsoft.com/en-us/library/aa260399(SQL.80).aspx
syscolumns http://msdn.microsoft.com/en-us/library/aa260398(SQL.80).aspx
sysobjects http://msdn.microsoft.com/en-us/library/aa260447(SQL.80).aspx

phil_mw60
Starting Member

4 Posts

Posted - 2010-04-08 : 11:30:05

Hi Russell,

Thanks, that query works a treat in my SQL 2000 database.

I don't suppose it's possible to include the Constraint definition in SQL 2000 in the same way as selecting the sys.default_constraints.definition column in SQL 2005 / 2008?

Thanks again,

Phil

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-04-08 : 12:40:14

look at the syscomments table.

http://msdn.microsoft.com/en-us/library/aa260393(SQL.80).aspx

select	o.name [table], c.name [column], object_name(d.constid) [constraint], cm.text
from	sysconstraints d
join	sysobjects o
on	o.id = d.id
join	syscolumns c
on	c.id = o.id
and	c.colid = d.colid
join	syscomments cm
on	cm.id = d.constid
where	d.status & 5 = 5
and	o.xtype = 'U'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources