how to find all the FKs of a table?

I need to delete all the rows of a table, but the table has FK and I get thi
s
error:
Cannot truncate table 'xxx' because it is being referenced by a FOREIGN KEY
constraint.
So first I want to delete all the FKs. How can I know which FKs were set in
this table? I have found something in the Diagram section, but can I list al
l
the FKs with a command?
ThanksHi
You can look at the INFORMATION_SCHEMA views such as
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
WHERE C.TABLE_SCHEMA = 'dbo'
AND C.TABLE_NAME = 'MyTable'
John
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks|||from an old post:
if object_id('usp_findreferences'_,'p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/ *****************************_**********
********************_**********/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table
1;s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/ *****************************_**********
********************_**********/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.tab_le_name,
c2.column_name
from information_schema.constraint__column_usage c1 join
information_schema.referential__constraints r on
c1.constraint_name=r.constrain_t_name
join information_schema.constraint__column_usage c2 on
r.unique_constraint_name=c2.co_nstraint_name
where c1.table_name=coalesce(@.tbname_,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.tab_le_name,
c2.column_name
from information_schema.constraint__column_usage c1 join
information_schema.referential__constraints r on
c1.constraint_name=r.unique_co_nstraint_name
join information_schema.constraint__column_usage c2 on
r.constraint_name=c2.constrain_t_name
where c1.table_name=coalesce(@.tbname_,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
-oj
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks