T-SQL OBJECT_ID always returning NULL even though metadata exists and user has correct permissions-CodePudding

I'm always getting null when I call OBJECT_ID with a valid object name. E.g.

use [My-Database];
declare @objectId int;

SELECT * FROM sys.objects WHERE name = 'DF_FieldStatusTypes_DisplayOrder'
SELECT @objectId = object_id FROM sys.objects WHERE name = 'DF_FieldStatusTypes_DisplayOrder'
SELECT @objectId
SELECT object_name(@objectId)
SELECT object_id(object_name(@objectId))
SELECT object_id('DF_FieldStatusTypes_DisplayOrder')

The results produced are as follows:

(1) DF_FieldStatusTypes_DisplayOrder| 753437758 | NULL | 5 | 241435934 | D | DEFAULT_CONSTRAINT | 2020-10-17 11:08:13.520 | 2020-10-17 11:08:13.520 | 0 | 0 | 0  
(2) 753437758  
(3) DF_FieldStatusTypes_DisplayOrder  
(4) NULL
(5) NULL

Results 1 through 3 are accurate, but 4 and 5 always return NULL. The Stack question I reference below is similar and quotes the Microsoft article for OBJECT_ID where it states:

... metadata-emitting, built-in functions such as OBJECT_ID may return NULL if the user does not have any permission on the object.

But, the exact same information is stated under the Exceptions section of the article for OBJECT_NAME

In my case however the OBJECT_NAME function is called successfully and OBJECT_ID always returns null.

What am I missing?

Similar Question

Why does object_id always returns null?

CodePudding user response：

This is a stab in the dark, but I suspect the hint of why is in your first result set. schema_id has a value of 5 which means it isn't on the dbo schema. As such I suspect that your default schema is not the schema that said object is on.

Take the following statements:

CREATE TABLE dbo.SomeTable (ID int
                                CONSTRAINT DF_ID
                                    DEFAULT 0);
GO

DECLARE @objectId int;

SELECT *
FROM sys.objects
WHERE name = 'DF_ID';

SELECT @objectId = object_id
FROM sys.objects
WHERE name = 'DF_ID';

SELECT @objectId;

SELECT OBJECT_NAME(@objectId);

SELECT OBJECT_ID(OBJECT_NAME(@objectId));

SELECT OBJECT_ID('DF_ID');
GO

DROP TABLE dbo.SomeTable;

This returns a non-NULL value for all data sets. db<>fiddle

Now let's use a different schema, that isn't my default.

CREATE SCHEMA another;
GO

CREATE TABLE another.SomeTable (ID int
                                CONSTRAINT DF_ID
                                    DEFAULT 0);
GO

DECLARE @objectId int;

SELECT *
FROM sys.objects
WHERE name = 'DF_ID';

SELECT @objectId = object_id
FROM sys.objects
WHERE name = 'DF_ID';

SELECT @objectId;

SELECT OBJECT_NAME(@objectId);

SELECT OBJECT_ID(OBJECT_NAME(@objectId));

SELECT OBJECT_ID('DF_ID');
GO

DROP TABLE another.SomeTable;
GO
DROP SCHEMA another

This, on the other hand returns the result you have: db<>fiddle

So, why is this? Well, OBJECT_NAME only returns the name of the object, not the name and schema. As such, DF_ID would be inferred to be the object DF_ID on the schema dbo; there is no such object on the dbo schema with that name.

If the schema name was included, you would get results: db<>fiddle

Of course, you would never use the object id, to get the object name, to get the object name, so the problem you have is a little nonsensical.

CodePudding user response：

You are missing the schema. From the sys.objects result it shows the object belongs to schema_id = 5. So try:

SELECT OBJECT_ID(SCHEMA_NAME(5)   N'.'   OBJECT_NAME(@objectId));

CodePudding user response：

What am I missing?

Your sys.objects query does not consider the object schema and will return rows regardless of the table schema. OBJECT_ID will return NULL when the table is not in your default schema and the object name is not qualified.

Try schema-qualifying the constraint name. dbo schema example:

SELECT OBJECT_ID('dbo.DF_FieldStatusTypes_DisplayOrder');