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
>

list Primary and foreign keys

I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.

Your help would make my life a lot easier

thanks

In 2005 you can simple perform the following query.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

There are a whole bunch of other system views that display similar information.

Larry Pope

List of Tables and Primary Key Foreign Key Names

Dear All,
I am trying to write some SQL that will give a list of all tables and
Primary Key/ Foreign Key constraints in a database. The code below goes part
of the way but not what I would like. It gives me:
Parent Table: Activities
Child Table: ActivitiesLocation
ForeignKey: 1
PrimaryKey: 17
TotalKeys: 1
(where 1 in the column name in the Activities table that is the Foreign Key
and column name 17 is where the Primary Key is to be found.)
Script:
SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS TotalKeys
FROM dbo.sysforeignkeys sf INNER JOIN
dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
dbo.sysobjects so2 ON so2.id = sf.fkeyid
ORDER BY so1.name
How can I get a list of the column name of column no 17 in the Activies
Table and column no 1 in the ActivitiesLocation table. Is there a better way
to get a listing of the PK/FK for a database along with table names?
Thanks again.
Alastair> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database.
http://www.aspfaq.com/search.asp?q=schema%3A|||Check out the information_schema.key_column_usage view. It should get you
started with what you need.
--Brian
(Please reply to the newsgroups only.)
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:9197A5C9-182B-451B-AE40-05DA1F98E62B@.microsoft.com...
> Dear All,
> I am trying to write some SQL that will give a list of all tables and
> Primary Key/ Foreign Key constraints in a database. The code below goes
> part
> of the way but not what I would like. It gives me:
> Parent Table: Activities
> Child Table: ActivitiesLocation
> ForeignKey: 1
> PrimaryKey: 17
> TotalKeys: 1
> (where 1 in the column name in the Activities table that is the Foreign
> Key
> and column name 17 is where the Primary Key is to be found.)
> Script:
> SELECT TOP 100 PERCENT so1.name AS 'Parent Table', so2.name AS 'Child
> Table', sf.fkey AS ForeignKey, sf.rkey AS PrimaryKey, sf.keyno AS
> TotalKeys
> FROM dbo.sysforeignkeys sf INNER JOIN
> dbo.sysobjects so1 ON so1.id = sf.rkeyid INNER JOIN
> dbo.sysobjects so2 ON so2.id = sf.fkeyid
> ORDER BY so1.name
> How can I get a list of the column name of column no 17 in the Activies
> Table and column no 1 in the ActivitiesLocation table. Is there a better
> way
> to get a listing of the PK/FK for a database along with table names?
> Thanks again.
> Alastair
>|||Thanks Aaron and Brian for both your replies. This group is definately on th
e
ball. Exactly what I wanted.
Alastair
"Aaron Bertrand [SQL Server MVP]" wrote:

> http://www.aspfaq.com/search.asp?q=schema%3A
>
>

List all Foreign Keys, Primary Keys, Indexes

With Microsoft SQL Server is there a way to list all Foreign Keys, Primary
Keys, Indexes etc. in addition to the database diagrams?
Thanks
Schema: How do I list all the indexes in a database?
http://www.aspfaq.com/show.asp?id=2541
Schema: How do I show all the primary keys in a database?
http://www.aspfaq.com/show.asp?id=2104
Schema: How do I find all the foreign keys in a database?
http://www.aspfaq.com/show.asp?id=2520
AMB
"Craig" wrote:

> With Microsoft SQL Server is there a way to list all Foreign Keys, Primary
> Keys, Indexes etc. in addition to the database diagrams?
>
> Thanks
>
>

List all Foreign Keys, Primary Keys, Indexes

With Microsoft SQL Server is there a way to list all Foreign Keys, Primary
Keys, Indexes etc. in addition to the database diagrams?
ThanksSchema: How do I list all the indexes in a database?
http://www.aspfaq.com/show.asp?id=2541
Schema: How do I show all the primary keys in a database?
http://www.aspfaq.com/show.asp?id=2104
Schema: How do I find all the foreign keys in a database?
http://www.aspfaq.com/show.asp?id=2520
AMB
"Craig" wrote:

> With Microsoft SQL Server is there a way to list all Foreign Keys, Primary
> Keys, Indexes etc. in addition to the database diagrams?
>
> Thanks
>
>

List all Foreign Keys, Primary Keys, Indexes

With Microsoft SQL Server is there a way to list all Foreign Keys, Primary
Keys, Indexes etc. in addition to the database diagrams?
ThanksSchema: How do I list all the indexes in a database?
http://www.aspfaq.com/show.asp?id=2541
Schema: How do I show all the primary keys in a database?
http://www.aspfaq.com/show.asp?id=2104
Schema: How do I find all the foreign keys in a database?
http://www.aspfaq.com/show.asp?id=2520
AMB
"Craig" wrote:
> With Microsoft SQL Server is there a way to list all Foreign Keys, Primary
> Keys, Indexes etc. in addition to the database diagrams?
>
> Thanks
>
>