OBJECTPROPERTY is local to the database the query is run it. So the object_id
passed in is resolved against master.sys.objects
: but the object_id comes from msdb
So here you have case 2.
On my server, I have 37 matching object_id values between msdb
and SomeDBOnMyServer
. But the names are different.
USE SomeDBOnMyServer
GO
SELECT
object_id,
name,
OBJECT_NAME(object_id)
FROM msdb.sys.objects
WHERE OBJECT_NAME(object_id) <> name
Obviously, I have a lot of rows where OBJECT_NAME(object_id)
is NULL that are filtered here
Its an underlying table for sys.partitions, which returns the same information as
SELECT * FROM sys.partitions
But what is a partition?
Refer to Partitioned Tables and Indexes on MSDN.
idmajor
is the column name that's commonly known as object_id
idminor
is the index_id.
Lets do some testing now: I have used AdventureWorks 2012 from CodePlex.
USE AdventureWorks2012_Data;
GO
DECLARE @MyID int;
SET @MyID = (SELECT OBJECT_ID('HumanResources.Employee',
'U'));
SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(@MyID);
Output:
name object_id type_desc
Employee 1237579447 USER_TABLE
Lets query the sys.partitions
view now:
SELECT partion_id, object_id, index_id FROM sys.partitions
WHERE object_id = '1237579447'
Output:
partition_id Object_id index_id
72057594045136896 1237579447 1
72057594050510848 1237579447 2
72057594050576384 1237579447 3
72057594050641920 1237579447 5
72057594050707456 1237579447 6
72057594050772992 1237579447 7
If we look at the sys.indexes
view:
SELECT object_id, name, index_id from sys.indexes where object_id = '1237579447'
Output:
Object_id name index_id
1237579447 PK_Employee_BusinessEntityID 1
1237579447 IX_Employee_OrganizationNode 2
1237579447 IX_Employee_OrganizationLevel_OrganizationNode 3
1237579447 AK_Employee_LoginID 5
1237579447 AK_Employee_NationalIDNumber 6
1237579447 AK_Employee_rowguid 7
In the above output, index_id is just an id for the indexes. 1 for Clustered index and the others (2-7) for other non clustered indexes.
The object_id for all indexes under a table is same as the object_id
for that table.
Lets take a look at the sys.sysrowsets
table:
USE AdventureWorks2012_data
GO
SELECT rowsetid, idmajor, idminor from sys.sysrowsets
WHERE idmajor = '1237579447'
I used the object_id for the HumanResources.Employee
table to filter data.
Output:
rowsetid idmajor idminor
-------------------- ----------- -----------
72057594045136896 1237579447 1
72057594050510848 1237579447 2
72057594050576384 1237579447 3
72057594050641920 1237579447 5
72057594050707456 1237579447 6
72057594050772992 1237579447 7
From this its clear that object_id
or idmajor
is the same for the table and all the indexes under it and idminor
is nothing but the index_id
for an index.
Best Answer
This deficiency in the docs was raised as an issue in August 2019, with MS comment being
Until such time as MS update the article (or accept a PR against the docs), here is a probably-partial list of the other possible values:
This Microsoft Support Document shows
referencing_class = 7
in relation to filtered indexes.Based on the Remarks section there are no others probably.