Primary key-Foreign key Tables

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

Transact-SQL (2005)

Primary key-Foreign key Tables

Author

Topic

degraft
Starting Member

10 Posts

Posted - 2009-08-17 : 10:16:09

Hi All,

I wrote a script to select all user defined tables in a database:
SELECT NAME FROM sys.objects WHERE Type = 'U'.

But I would now want to select all the other tables that are related to a particular table based on the primary key-foreign key relationship. i.e if I select a table, I should be able to view the tables that are related to it.

Thanks very much in advance.

Regards,
Degraft.

DD

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 10:46:34

One simple way is to use:

exec sp_help <table_name>

it returns multiple result sets with a lot of info about the table.

Be One with the Optimizer
TG

degraft
Starting Member

10 Posts

Posted - 2009-08-17 : 11:08:23

Hi TG,

Thanks so much for the help.
I've executed that with a test database and it gave me a lot of information about the table including the Tables referenced by foreign key. But what should I do to get ONLY the names of the Tables referenced by the foreign key because I really dont need the other information.

Thanks.
Degraft.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 11:11:45

One thing you could do is look at the code of sp_help (from master system SPs) and create your own script with using just the portion you want. It is a good way to learn something about the system tables as well.

Be One with the Optimizer
TG

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-17 : 11:32:27

or can just look in sys.foreign_keys table


Declare @tbl sysname
Set @tbl = 'YourTable'

SELECT	object_name(referenced_object_id), 'refers to'  from sys.foreign_keys where parent_object_id = object_id(@tbl)
union all
SELECT	object_name(parent_object_id), 'referenced by' from sys.foreign_keys where referenced_object_id = object_id(@tbl)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 11:39:21

Or this:


select table_name =         object_name(fc.parent_object_id) 
       ,column_name =       pc.name
       ,referenced_table =  object_name(fc.referenced_object_id)
       ,referenced_column = rc.name             
from   sys.foreign_key_columns fc
join   sys.syscolumns rc
       on rc.id = fc.referenced_object_id
       and rc.colid = fc.referenced_column_id
join   sys.syscolumns pc
       on pc.id = fc.parent_object_id
       and pc.colid = fc.parent_column_id
where  parent_object_id = object_id('<yourTable>')

Be One with the Optimizer
TG

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-18 : 00:03:31

Hi Try this once,

select * from sys.objects where type = 'pk'

select * from sys.objects where type = 'f'

degraft
Starting Member

10 Posts

Posted - 2009-08-18 : 05:50:24

Hi All,
Thank you very much for the assistance.

TG, thanks so much for the information on the master system SPs. I will make time and get into details with it. Well the script you gave me run but brought only one record:

select table_name = object_name(fc.parent_object_id)
,column_name = pc.name
,referenced_table = object_name(fc.referenced_object_id)
,referenced_column = rc.name
from sys.foreign_key_columns fc
join sys.syscolumns rc
on rc.id = fc.referenced_object_id
and rc.colid = fc.referenced_column_id
join sys.syscolumns pc
on pc.id = fc.parent_object_id
and pc.colid = fc.parent_column_id
where parent_object_id = object_id('tbCommunities')

Result:
tbCommunities DistrictID tbDistricts DistrictID

Russel, your script did exactly what i wanted only that the first record brought a table that my primary table is linked to. I woudn't want that one.

Declare @tbl sysname
Set @tbl = 'tbCommunities'

SELECT object_name(referenced_object_id), 'refers to' from sys.foreign_keys where parent_object_id = object_id(@tbl)
union all
SELECT object_name(parent_object_id), 'referenced by' from sys.foreign_keys where referenced_object_id = object_id(@tbl)

Result:
tbDistricts refers to
tbEducation referenced by
tbPFIs referenced by
tbPipeSource referenced by
tbPointWaterSource referenced by
tbPreCoolers referenced by
tbMarkets referenced by
tbStandPipes referenced by

I actually found another script that did just what i needed and would like to share it too:

select
tblAll.table_name as PrimaryTableName,
tblAll.column_name as PrimaryTableColumn,
tblFK.table_name as ForeignKeyTable,
tblFK.column_name as ForeignKeyColumn
from information_schema.constraint_column_usage tblAll
inner join information_schema.referential_constraints tblAllFK on tblAllFK.unique_constraint_name = tblAll.constraint_name
inner join information_schema.constraint_column_usage tblFK on tblAllFK.constraint_name=tblFK.constraint_name
Where tblAll.table_name='tbCommunities'

tbCommunities CommID tbEducation CommID
tbCommunities CommID tbMarkets CommID
tbCommunities CommID tbPFIs CommID
tbCommunities CommID tbPipeSource CommID
tbCommunities CommID tbPointWaterSource CommID
tbCommunities CommID tbPreCoolers CommID
tbCommunities CommID tbStandPipes CommID

Hey Thanks so much you guy. I appreciate it.

DD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources