CODEWITHSUNDEEP
sqlsql-server
Alexander
Alexander

Reputation: 20224

Get object id of columns

I want to remove the old and insert a new foreign key if the old one is on the wrong column:

SELECT * FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON (fk.object_id = fkc.constraint_object_id)
WHERE fk.parent_object_id = OBJECT_ID(@Table)
AND fk.referenced_object_id = OBJECT_ID(@RefTable)
AND (fkc.parent_object_id != OBJECT_ID(@FieldName) OR fkc.referenced_object_id != OBJECT_ID(@RefFieldName))

But both OBJECT_ID(@FieldName) and OBJECT_ID(@RefFieldName) always return NULL, although the column exists - how can I get the object id of columns?

Upvotes: 1

Views: 4487

Answers (1)

SMM
SMM

Reputation: 2235

Columns don't have object_ids - they have column_ids.

FKs match parent_object_id to referenced_object_id (have matching object_ids in sys.objects to get names) AND parent_column_id to referenced_column_id (use x_object_id and x_column_id to lookup in sys.columns for object_id and column_id).

I'm not sure how you can determine which is the "wrong" column though since column names don't have to match in a FK relationship unless you are guaranteed to have exact names. For an example of where they might not match note the previous paragraph where we have referenced_object_id which would reference object_id.

Upvotes: 2

Related Questions