Table names involved in a CONSTRAINT

Question

Post reply

Table names involved in a CONSTRAINT

Miquel Martí

Ten Centuries

Points: 1217
More actions
January 23, 2006 at 2:20 am

#152708

Hello!!
Any has a clue on how to
Select the two table names involved in a FK constraint?
I can only catch parent table name, with
SELECT object_name(parent_obj)
FROM sysobjects

Thank you a lot!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

[KH] SSCommitted Points: 1908 More actions · Answer 1

select object_name(fkeyid) as parent_table, object_name(rkeyid) as fkey_table

from sysreferences

where constid = (select id from sysobjects where name = 'foreign_key_name')

Miquel Martí Ten Centuries Points: 1217 More actions · Answer 2

oh, yes

I didn't know about sysreferences table

Just for thread documenting pourposes, I'll explain what intended:

-- Look for wrong FK nomenclature

SELECT 'FK_' + object_name(fkeyid) + '__' + object_name(rkeyid) AS correct_name

, object_name(fkeyid) as parent_table

, object_name(rkeyid) as fkey_table

, sysobjects.*

FROM sysobjects,

sysreferences

WHERE constid = id

AND xtype='F'

AND name <> 'FK_' + object_name(fkeyid) + '__' + object_name(rkeyid) --look for wrong nomenclature

-- Look for wrong PK nomenclature

SELECT 'PK_' + object_name(parent_obj) AS correct_name, *

FROM sysobjects

WHERE xtype='PK'

AND name <> 'PK_' + object_name(parent_obj)--look for wrong nomenclature

Thanks again!

Lowell SSC Guru Points: 323546 More actions · Answer 3

here's a version that i use; it includes the alter table add constraint format as well, in case you were readding them to your database or whatever.

select

sysobjects.name as fkname1,

sysobjects.id as fkid1,

object_name(sysforeignkeys.fkeyid) as fktable1,

col_name(sysforeignkeys.fkeyid,fkey) as fkcol1,

object_name(sysforeignkeys.rkeyid) as reftable1,

col_name(sysforeignkeys.rkeyid,sysforeignkeys.rkey) as refcol1,

'ALTER TABLE ' + object_name(sysforeignkeys.fkeyid) + ' ADD CONSTRAINT ' + sysobjects.name + ' FOREIGN KEY (' + col_name(sysforeignkeys.fkeyid,fkey) + ') REFERENCES ' + object_name(sysforeignkeys.rkeyid) + ' (' + col_name(sysforeignkeys.rkeyid,sysforeignkeys.rkey) + ')'

from sysobjects

inner join sysforeignkeys

on sysobjects.id=sysforeignkeys.constid

where sysobjects.xtype='F'

order by fktable1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!