how to view tables which have primary or foreign k

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to view tables which have primary or foreign k

Author

Topic

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-06 : 12:23:58

i need a list of tables from my database which have primary or foreign keys.

select * from sysobjects where type = 'U'

gives me a list of all the tables. How do i get the list of tables with primary or foreign or both keys and the name of the keys if possible

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-06 : 12:39:35

[code]
select
*
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
CONSTRAINT_TYPE in ('PRIMARY KEY','FOREIGN KEY')

[/code]

CODO ERGO SUM

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-06 : 12:57:46

i don't have access to INFORMATION_SCHEMA. Can I used sysobjects to do that.
I have limited access to the database

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-06 : 13:02:01

Also I need to see the name of the table along with name of the key

something like this

select name, type from sysobjects where type = 'RI'

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-06 : 13:11:31

select sc.name, so.name
FROM sysconstraints sc, sysobjects so
where
so.type = 'RI' and sc.id=so.id

does not work, need something similar

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-06 : 20:41:21

quote:
Originally posted by sqldev80

i don't have access to INFORMATION_SCHEMA. Can I used sysobjects to do that.
I have limited access to the database

What was the error message you got when you ran the code I posted?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources