List of Tables and Primary Key Foreign Key Names

2 Sep 2005 2:21 PM

Alastair MacFarlane

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

2 Sep 2005 2:26 PM

Aaron Bertrand [SQL Server MVP]

> 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

2 Sep 2005 2:45 PM

Alastair MacFarlane

Thanks Aaron and Brian for both your replies. This group is definately on the
ball. Exactly what I wanted.

Alastair

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

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

2 Sep 2005 2:32 PM

Brian Lawton

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

Show quote

"Alastair MacFarlane" <AlastairMacFarl***@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
>