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

Hi
Does anyone know how to generate a list of tables' primary keys, for all tables in a database? Is there a stored procedure or similar
Thanks
Mattuse database
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Ana
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt|||Matt,
use pubs
go
select tc.TABLE_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
on (tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA and
tc.TABLE_NAME = kcu.TABLE_NAME and
tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME)
where tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by tc.TABLE_NAME, kcu.ORDINAL_POSITION
Chief Tenaya
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt|||Hi,
Execute the query from the database you require the primary key details;
select * from information_schema.table_constraints where
constraint_type='Primary Key'
Thanks
Hari
MCDBA
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt

List of primary keys

Hi,
Does anyone know how to generate a list of tables' primary keys, for all tables in a database? Is there a stored procedure or similar?
Thanks,
Matt
use database
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Ana
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt
|||Matt,
use pubs
go
select tc.TABLE_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
on (tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA and
tc.TABLE_NAME = kcu.TABLE_NAME and
tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME)
where tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by tc.TABLE_NAME, kcu.ORDINAL_POSITION
Chief Tenaya
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt
|||Hi,
Execute the query from the database you require the primary key details;
select * from information_schema.table_constraints where
constraint_type='Primary Key'
Thanks
Hari
MCDBA
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt
|||Thanks for all your speedy responses!
Matt
http://www.matthewbarr.co.uk/

List of primary keys

Hi,
Does anyone know how to generate a list of tables' primary keys, for all tab
les in a database? Is there a stored procedure or similar?
Thanks,
Mattuse database
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Ana
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt|||Matt,
use pubs
go
select tc.TABLE_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
on (tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA and
tc.TABLE_NAME = kcu.TABLE_NAME and
tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME)
where tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by tc.TABLE_NAME, kcu.ORDINAL_POSITION
Chief Tenaya
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt|||Hi,
Execute the query from the database you require the primary key details;
select * from information_schema.table_constraints where
constraint_type='Primary Key'
Thanks
Hari
MCDBA
"Matt" <m.barr@.rsamd.ac.uk> wrote in message
news:7F785B2D-D9D3-4848-B336-8789264BABEB@.microsoft.com...
> Hi,
> Does anyone know how to generate a list of tables' primary keys, for all
tables in a database? Is there a stored procedure or similar?
> Thanks,
> Matt|||Thanks for all your speedy responses!
Matt
http://www.matthewbarr.co.uk/

List of keys, indexes

Hello,

I am new in SQL Server, I have to deal with this big database with many tables, is there any way I can get a list of all primary keys , foreign keys and indexes and on all tables?

Thanks,

I highly suggest downloading Microsoft'sSQL Server 2000 Books Online. This is a free SQL Server 2000 reference and it is invaluable. You can also access it online:Getting Started with SQL Server Books Online, although it is much easier to work with as a local installation. Check out theInformation Schema Views topic.

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