Listing foreign keys

Hi all,
I've got a couple of queries that I can't figure out exactly how to build
them. Both go over the 'sys' tables.
All my tables have the primarykey called 'id_no'. So if I have a table
'customers', the identity field is called 'id_no'. If I have a second table
'orders' with a foreign key on 'customers', the field is called
'id_no_customer'...an so on...
I need 2 queries:
1. The first should return a list of those columns (including source and
target tables) that are defined as foreignkeys (ie. columnname starts with
'id_no_') but no foreignkey actually exacts.
2. The second query should return a list of foreignkeys with the following
information:
target_table
target_column
source_table
source_column
I've tried the following, but on a table of 3 foreignkeys (1 is actually
missing), I get a result of 50, instead of just 3.
select target_table.name, target_column.name, source_table.name,
source_column.name from sysforeignkeys
inner join sysobjects target_table on sysforeignkeys.fkeyid =
target_table.id
inner join syscolumns target_column on sysforeignkeys.fkeyid =
target_column.id
inner join sysobjects source_table on sysforeignkeys.rkeyid =
source_table.id
inner join syscolumns source_column on sysforeignkeys.fkeyid =
source_column.id
where target_table.name = 'xxx'
Thanks for help!
IvanIvan
If I remember well , OJ had written this script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_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.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_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'
drop proc usp_findreferences
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eWrQJySuFHA.3684@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I've got a couple of queries that I can't figure out exactly how to build
> them. Both go over the 'sys' tables.
> All my tables have the primarykey called 'id_no'. So if I have a table
> 'customers', the identity field is called 'id_no'. If I have a second
> table
> 'orders' with a foreign key on 'customers', the field is called
> 'id_no_customer'...an so on...
> I need 2 queries:
> 1. The first should return a list of those columns (including source and
> target tables) that are defined as foreignkeys (ie. columnname starts with
> 'id_no_') but no foreignkey actually exacts.
> 2. The second query should return a list of foreignkeys with the following
> information:
> target_table
> target_column
> source_table
> source_column
> I've tried the following, but on a table of 3 foreignkeys (1 is actually
> missing), I get a result of 50, instead of just 3.
> select target_table.name, target_column.name, source_table.name,
> source_column.name from sysforeignkeys
> inner join sysobjects target_table on sysforeignkeys.fkeyid =
> target_table.id
> inner join syscolumns target_column on sysforeignkeys.fkeyid =
> target_column.id
> inner join sysobjects source_table on sysforeignkeys.rkeyid =
> source_table.id
> inner join syscolumns source_column on sysforeignkeys.fkeyid =
> source_column.id
> where target_table.name = 'xxx'
> Thanks for help!
> Ivan
>|||I solved the 2nd query this way:
select target_table.name as target_table, target_column.name as
target_column,
source_table.name as source_table, source_column.name as source_column from
sysforeignkeys
inner join sysobjects target_table on sysforeignkeys.fkeyid =
target_table.id
inner join syscolumns target_column
on sysforeignkeys.fkeyid = target_column.id
and sysforeignkeys.fkey = target_column.colid
inner join sysobjects source_table on sysforeignkeys.rkeyid =
source_table.id
inner join syscolumns source_column
on sysforeignkeys.rkeyid = source_column.id
and sysforeignkeys.rkey = source_column.colid
where target_table.name = 'xxx'
But I now have to solve the first query.
Ivan
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:OUrX$1SuFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Ivan
> If I remember well , OJ had written this script
> create procedure usp_findreferences
> @.tbname sysname=null
> as
> set nocount on
>
> Print 'Referenced:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referenced_parent_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.constraint_name
> join information_schema.constraint_column_usage c2 on
> r.unique_constraint_name=c2.constraint_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.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.unique_constraint_name
> join information_schema.constraint_column_usage c2 on
> r.constraint_name=c2.constraint_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'
> drop proc usp_findreferences
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eWrQJySuFHA.3684@.TK2MSFTNGP09.phx.gbl...
build
with
following
>