OBJECT_ID() works for all objects BUT views?-CodePudding

Here I want to retrieve OBJECT_ID of a view as I do with my other objects. But am facing the fact that it returns NULL instead

SELECT TOP 1 1 FROM sys.views WHERE object_id = OBJECT_ID('vw202004-divvy-tripdata.csv')

/*
Result:

NULL

*/

Here is shown that the object exists.. Note: I've tried to include DatabaseName and/or SchemaName before ObjectName.

SELECT 
  name
  , type
  , object_id
  , [id] = OBJECT_ID(name, type)
FROM sys.views

/*
Result:

name    type    object_id   id
vw202004-divvy-tripdata.csv V   1136136680  NULL

*/

CodePudding user response：

Your view name includes "." and "-", in order to get the actual object name you need to add square brackets (unless your schema is vw202004-divvy-tripdata)

try:

SELECT * FROM sys.views WHERE object_id = object_id('[vw202004-divvy-tripdata.csv]')

But you don't need to go all the way to sys.views to extract the object_id from the view name just try::

SELECT object_id('[vw202004-divvy-tripdata.csv]', 'V')

CodePudding user response：

I believe OBJECT_ID returns NULL as its return value is INT and 1136136680 is greater than INT's maximum datalength.