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
>
>