Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts

Monday, March 12, 2012

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

Friday, March 9, 2012

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.

Monday, February 20, 2012

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