I have a strange issue in MS SQL Server 2012. I'm trying to check if a foreign key already exist in an upgrade script. I've used the system OBJECT_ID() function in the past to find tables, views and procedures, but when I try to use it to find a foreign key it does not work.
-- This query always returns null
SELECT OBJECT_ID(N'FK_Name', N'F')
-- This query works, returning the object ID for the foreign key
SELECT object_id FROM sys.foreign_keys WHERE name=N'FK_Name'
This SO answer suggests that my OBJECT_ID() query should work.
Well it could be that your foreign key is looking to the table not in default schema (probably dbo). In this case you'll not see object_id until you specify schema, like this:
SELECT OBJECT_ID(N'<schema>.FK_Name', N'F')
Actually, you could have multiple objects with the same name in your database, but within different schemas. OBJECT_ID(N'FK_Name', N'F') will return id of object in the default schema.
You can test it like this:
create schema test
create table test.temp1 (id int primary key)
create table test.temp2 (id int)
go
alter table test.temp2 add constraint FK_temp foreign key(id) references test.temp1(id)
select object_id('FK_temp', 'F') -- returns null
select object_id('test.FK_temp', 'F') -- returns object id
drop table test.temp2
drop table test.temp1
drop schema test