Get the table which does not have Foreign Key

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)

Get the table which does not have Foreign Key

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-11-30 : 00:19:19

Hi,

Consider my table test have 3 columns a, b, c
a is already have a foreign key. But b and c does not have any foreign key. But the same column already exists in some other table which is in the primary key table.

I want to find out the table whose columns already exists in the primary key table and does not have foreign key.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-11-30 : 01:22:32

U can get the info by using INFORMATION_SCHEMA views. start with this[CODE]SELECT C.TABLE_NAME, C.COLUMN_NAME, CP.TABLE_NAME AS PK_TABLE_NAME, CP.COLUMN_NAME AS PK_COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.COLUMNS CP ON C.TABLE_NAME <> CP.TABLE_NAME
AND C.COLUMN_NAME = CP.COLUMN_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON CCU.TABLE_NAME = CP.TABLE_NAME
AND CCU.COLUMN_NAME = CP.COLUMN_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_NAME = CCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME[/CODE]

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

baburk
Posting Yak Master

108 Posts

Posted - 2009-11-30 : 22:31:12

Thanks for your reply. Give me some explination.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources