Internally, how does OpenRowset(TABLE ...) work?

I see a lot of internal views, such as sys.syscomments making calls to CROSS APPLY OpenRowset(TABLE, oid). I am wondering how this function operates and what it's reading from. Let's look at one example with TABLE SQLSRC,

• sys.syscomments

• OpenRowset(TABLE SQLSRC, o.id, 0) (o is sys.sysschobjs$)

• OpenRowset(TABLE SQLSRC, c.object_id, c.column_id) (c is sys.computed_columns)

• OpenRowset(TABLE SQLSRC, p.object_id, p.procedure_number) (p is sys.numbered_procedures)

• sys.all_extended_procedures

• OpenRowset(TABLE SQLSRC, o.object_id, 0) (o is sys.all_objects)

• sys.sysfulltextcatalogs

• OpenRowset(TABLE SQLSRC, o.id, 0) (o is sys.sysschobjs$)

It would seem like it's storing the source code for the procedures, but internally what are they reading from and what is TABLE SQLSRC? I'm guessing this is an out-of-line key-value store?

I know I can use object_definition (which is what sys.sql_modules calls internally) but I'm wanting to understand how the database works and where this is stored.

And, it's not just SQLSRC either there is also TABLE references to the following,

```
ACTIVE_TRANSACTIONS
ALUCOUNT
APRC_EVENT
BLOB_CONTAINER_ACCESSOR
BUILTINPERMISSIONS
CFGPROP
CHARSET
CHILDINSTANCES
COLLATIONS
COLUMNSTORE_OPERATIONAL_STATS
COLUMNSTORE_ROW_GROUPS
COLUMNSTORE_TUPLE_MOVER
DATABASEENCRYPTIONKEYS
DATABASE_AUTOMATIC_TUNING_ADVISOR_OPTIONS
DATABASE_AUTOMATIC_TUNING_MODE
DATABASE_FILESTREAM_OPTIONS
DATABASE_TRANSACTIONS
DATABASE_TUNING_RECOMMENDATIONS
DBLOG
DBMIRROR
DBMIRROR_AUTO_PAGE_REPAIR
DBMIRROR_PAST_ACTIONS
DBMIRROR_WITNESS
DBPROP
DBRECOVER
DBSCRIPTLEVEL
DB_SCOPED_CONFIG
DB_STORAGE_VOLUME_PROPS
DIAGLOG_CONFIGS
DMF_SP_DESCRIBE_FIRST_RESULT_SET_OBJECT
DMF_SP_DESCRIBE_FIRST_RESULT_SET_STRING
DM_AUDIT_ACTIONS
DM_AUDIT_CLASS_TYPE_MAP
DM_AVAILABILITY_DATABASES_CLUSTER
DM_AVAILABILITY_GROUPS
DM_AVAILABILITY_GROUPS_CLUSTER
DM_CDC_ERRORS
DM_CDC_LOGSCAN_SESSIONS
DM_CLOUD_PHYSICAL_SEEDING_STATS
DM_CLR_APPDOMAINS
DM_CLR_

I think this article is the nearest you will come to determining what goes on under the hoods:

Where Does SQL Server Store the Source for Stored Procedures? (improve.dk)

In his article Mark S. Rasmussen digs into finding out where several (SQL Server) internal views retrieve base definitions from. Mark achieves this by analysing a self created procedure XYZ.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        
-- Create date: 
-- Description:   
-- =============================================
CREATE PROCEDURE XYZ
  AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  SELECT 'AABBCC' AS Output
END

He then retrieves the information for that procedure from the sys.procedures table:

select * from sys.procedures

...and commences to the sys.sql_modules view:

select * from sys.sql_modules where object_id = 

That shows the source code of the new procedure XYZ.

But where does the system store the definition of the sys.sql_modules view? This can be retrieved by issuing the following command:

select object_definition(object_id('sys.sql_modules'))

This will reveal some vital information. The sys.sql_modules view relies on the object_definition system function. Here Mark hits a wall and has to circle round via the sys.syscomments view (deprecated) to reveal:

...
CROSS APPLY
    OpenRowset(TABLE SQLSRC, c.object_id, c.column_id) s  
...

Mark's previous endeavours into finding internal information led through detaching the database and using a HEX editor to find the string of the procedure AABBCC. Then using the HEX offset to calculate the ID of the page and then via DBCC PAGE to the reveal the ObjectID of the item he was searching for which in this case is then sys.sysobjvalues.

From there he retrieves the data from sys.sysobjvalues fist via the statement:

select * from sys.sysobjvalues where objid = 2105058535

...and additionally via:

select convert(varchar(max), imageval) from sys.sysobjvalues where objid = 2105058535

_2105058535 being the object_id of his procedure_

My musings

Now if you take the information retrieved with Mark's analysis and compare that to the original statement using the ...OPENROWSET(TABLE... code, it is probably safe to assume the following:

OpenRowset(TABLE SQLSRC, p.object_id, p.procedure_number) s is similar/related to sys.sysobjvalues because the data retrieved contains the source code for the dummy Procedure XYZ containing the string AABBCC.

SELECT
o.id AS id,
convert(smallint, case when o.type in ('P', 'RF') then 1 else 0 end) AS number,
s.colid,
s.status,
convert(varbinary(8000), s.text) AS ctext,
convert(smallint, 2) AS texttype,
convert(smallint, 0) AS language,
sysconv(bit, 0) AS encrypted,
sysconv(bit, 0) AS compressed,
s.text
FROM
sys.sysobjrdb o
CROSS APPLY
OpenRowset(TABLE SQLSRC, o.id, 0) s
WHERE
db_id() = 1 AND
o.type IN ('P','V','X','FN','IF','TF')

The s.text value from above being the same as imageval in:

select convert(varchar(max), imageval) from sys.sysobjvalues where objid = 2105058535

What exactly OpenRowset(TABLE SQLSRC, o.id, 0) does is probably an internal call to an object in the sqlserver.exe process or one of its linked libraries.

StackExchange Database Administrators Q#195099, answer score: 10