CODEWITHSUNDEEP
sql-server
Andrew C
Andrew C

Reputation: 1260

OBJECT_ID fails to find primary key

I have a primary key called "PK_ABC.Sample" and I'm trying to find if it exists. For some reason I keep getting null when I call OBJECT_ID. For a quick test I changed the SQL to the following.

SELECT OBJECT_ID('PK_ABC.Sample') as 'Object Id'

I get a null result, which I understand means one of two things: it doesn't exist or I don't have permissions to see it.

Checking the sys.objects table I see that I have an entry called "PK_ABC.Sample" which has an object_id of 123 and type "PK".

Running the opposite operation OBJECT_NAME(123) results in the correct name "PK_ABC.Sample".

Why do I not get the value 123 when I run OBJECT_ID?

Upvotes: 0

Views: 2075

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

You'll need to enclose the name in brackets due to the period in the object name:

SELECT OBJECT_ID('[PK_ABC.Sample]');

I suggest you name objects following the rules for regular identifers (https://msdn.microsoft.com/en-us/library/ms175874.aspx) to avoid this issue and make maintenance easier.

EDIT:

You added that ABC is the schema name, so you need to schema-qualify the name if is not in your default schema:

SELECT OBJECT_ID(N'ABC.[PK_ABC.Sample]');

I suspect someone accidentally (re)named the constraint as "PK_" followed by the schema-qualified table name. This is redundant because table and column-level constraints are implicitly in the same schema as the parent table. The intended name is likely "PK_Sample", which conforms to the identifier naming I mentioned earlier.

The redundant schema name and period can be removed with a rename:

EXEC sp_rename N'ABC.[PK_ABC.Sample]', N'PK_Sample';

You can then get the object id without the brackets and confusion:

SELECT OBJECT_ID(N'ABC.PK_Sample');

Upvotes: 4

Related Questions