Sql-server – Internally, how does OpenRowset(TABLE …) work

database-internalssql server

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_LOADED_ASSEMBLIES
DM_CLR_PROPERTIES
DM_CLR_TASKS
DM_COLUMNSTORE_OBJECT_POOL_STATS
DM_CRYPTO_PROVIDER_PROPS
DM_DB_DATABASE_ALLOCATIONS
DM_DB_FILE_SPACE_USAGE
DM_DB_LOG_SPACE_USAGE
DM_DB_LOG_STATS
DM_DB_MODULE_CONTAINMENT_BREACHING_FEATURES
DM_DB_RDA_MIGRATION_STATUS
DM_DB_RDA_SCHEMA_UPDATE_STATUS
DM_DB_SEQUENCES
DM_DB_SESSION_SPACE_USAGE
DM_DB_STATS_HISTOGRAM
DM_DB_STATS_PROPERTIES
DM_DB_TASK_SPACE_USAGE
DM_DB_VLF_INFO
DM_EXEC_BACKGROUND_JOB_QUEUE
DM_EXEC_BACKGROUND_JOB_QUEUE_STATS
DM_EXEC_CURSORS
DM_EXEC_PARALLEL_WORKERS
DM_EXEC_QE_GRANTSINFO
DM_EXEC_QE_RESSEM
DM_EXEC_QUERY_PROFILES
DM_EXEC_QUERY_STATS_XML
DM_EXEC_TEXT_QUERY_PLAN
DM_EXEC_XML_HANDLES
DM_EXTSCRIPT_EXEC_STATS
DM_EXTSCRIPT_REQUESTS
DM_FILESTREAM_FILE_IO_HANDLES
DM_FILESTREAM_FILE_IO_REQUESTS
DM_FILESTREAM_NON_TRANSACTED_HANDLES
DM_HADR_AUTOMATIC_SEEDING
DM_HADR_AUTO_PAGE_REPAIR
DM_HADR_AVAILABILITY_REPLICA_CLUSTER_NODES
DM_HADR_AVAILABILITY_REPLICA_CLUSTER_STATES
DM_HADR_CLUSTER
DM_HADR_CLUSTER_MEMBERS
DM_HADR_CLUSTER_NETWORKS
DM_HADR_DATABASE_REPLICA_CLUSTER_STATES
DM_HADR_DATABASE_REPLICA_STATES
DM_HADR_INSTANCE_NODE_MAP
DM_HADR_INTERNAL_AG_LISTENERS
DM_HADR_INTERNAL_AG_LISTENER_ADDRESSES
DM_HADR_INTERNAL_AG_READONLY_ROUTING_LIST
DM_HADR_INTERNAL_AVAILABILITY_GROUP_STATES
DM_HADR_INTERNAL_AVAILABILITY_REPLICA_STATES
DM_HADR_INTERNAL_TRANSPORT_PARTNERS
DM_HADR_INTERNAL_WSFC_AGS
DM_HADR_INTERNAL_WSFC_AG_DB_REPLICAS
DM_HADR_INTERNAL_WSFC_AG_LOGICAL_DBS
DM_HADR_INTERNAL_WSFC_AG_REPLICAS
DM_HADR_NAME_ID_MAP
DM_LOGCONSUMER_CACHEBUFFERREFS
DM_LOGCONSUMER_PRIVATECACHEBUFFERS
DM_LOGPOOLMGR_FREEPOOLS
DM_LOGPOOLMGR_RESPOOLSIZE
DM_LOGPOOLMGR_STATS
DM_LOGPOOL_CONSUMERS
DM_LOGPOOL_HASHENTRIES
DM_LOGPOOL_SHAREDCACHEBUFFERS
DM_LOGPOOL_STATS
DM_OS_BPOOLEXTENSION_CONFIG
DM_OS_DISPATCHERPOOLS
DM_OS_DISPATCHERS
DM_OS_ENUM_FILESYSTEM
DM_OS_ENUM_FIXED_DRIVES
DM_OS_FILE_EXISTS
DM_OS_HOST_INFO
DM_OS_MEMORYNODEACCESSSTATS
DM_OS_MEMORY_BROKERS
DM_OS_MEMORY_BROKER_CLERKS
DM_OS_MEMORY_NODES
DM_OS_MEMORY_POOLS
DM_OS_NODES
DM_OS_PROCESS_MEMORY
DM_OS_SPINLOCKSTATS
DM_OS_SUBLATCHES
DM_OS_SYS_MEMORY
DM_OS_WINDOWS_INFO
DM_QO_GATES
DM_REPL_ARTICLES
DM_REPL_SCHEMAS
DM_REPL_TRANHASH
DM_REPL_TRANINFO
DM_RG_CONFIGURATION
DM_RG_EXTPOOLS
DM_RG_EXTPOOL_AFFINITY
DM_RG_GROUPS
DM_RG_POOLS
DM_RG_POOL_AFFINITY
DM_RG_POOL_VOLUMES
DM_SERVER_AUDIT_STATUS
DM_SERVER_MEMORY_DUMPS
DM_SERVER_REGISTRY
DM_SERVER_SERVICES
DM_SQL_REFERENCED_ENTITIES
DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS
DM_TRAN_CURRENT_SNAPSHOT
DM_TRAN_CURRENT_TRANSACTION
DM_TRAN_TRANSACTIONS_SNAPSHOT
DM_TRAN_VERSION_STORE
DM_TRAN_VERSION_STORE_SPACE_USAGE
DM_XE_MAP_VALUES
DM_XE_OBJECTS
DM_XE_OBJECT_COLUMNS
DM_XE_PACKAGES
DM_XE_SESSIONS
DM_XE_SESSION_EVENTS
DM_XE_SESSION_EVENT_ACTIONS
DM_XE_SESSION_OBJECT_COLUMNS
DM_XE_SESSION_TARGETS
DSIPOP
EXPAND_EVENTS
FCI_CONFIGS
FILETABLES
FNGETPLANATTRIBS
FNGETQUERYPLAN
FNGETSQL
FN_DB_BACKUP_FILE_SNAPSHOTS
FN_GET_AUDIT_FILE
FN_HADR_DISTRIBUTED_AG_DB_REPLICA
FN_HADR_DISTRIBUTED_AG_REPLICA
FN_MSXE_READ_EVENT_STREAM
FN_XE_PREDICATE_XML
FN_XE_READ_FILE_TARGET_FILE
FTBATCHES
FTCATALOGS
FTCATCOMPONENTS
FTCOMPINDEX
FTCRAWLRANGES
FTCRAWLS
FTFDHOST
FTMATCHQUERY
FTMEMBUFFERS
FTMEMPOOLS
FTSYSSTPWD
FTTHESAURUSFILES
FTWORDBREAKER
FUNCTION_STATS
GLOBAL_TRANSACTIONS
GLOBAL_TRANSACTIONS_ENLISTMENTS
GLOBAL_TRANSACTIONS_LOG
GLOBAL_TRANSACTIONS_RECOVERY
HKCS_DICTIONARIES
HKCS_SEGMENTS
HK_HOST_BLOCKALLOC
INDEXCOUNT
INDEXPROP
INDEX_RESUMABLE_OPERATIONS
LOGINDEXSTATS
LOGINROLESANDGROUPS
MISSING_IDX_COLUMNS
MISSING_IDX_DETAILS
MISSING_IDX_GROUPS
NETGROUPGETMEMBERS
NETUSERGETGROUPS
OPENKEYS
OPENTAPES
OPTIMIZERINFO
PARTITIONCOUNTS
PLANGUIDES
PROC_STATS
QUERY_STATS
QUERY_STORE_CONTEXT_SETTINGS
QUERY_STORE_OPTIONS
QUERY_STORE_PLAN_IN_MEM
QUERY_STORE_QUERY_IN_MEM
QUERY_STORE_QUERY_TEXT_IN_MEM
QUERY_STORE_RUNTIME_STATS_INTERVAL
QUERY_STORE_RUNTIME_STATS_IN_MEM
QUERY_STORE_SQL_STATEMENT_HANDLE
QUERY_STORE_WAIT_STATS_IN_MEM
RSCPROP
SBACTIVATEDTASKS
SBCONNECTIONENDPOINTS
SBFORWARDEDMESSAGES
SBQUEUEMONITORS
SECURABLECLASSES
SELECTIVE_XML_INDEX_PATHS
SERVERSHAREDDRIVES
SERVERSHAREDVALIDPATHS
SQLSRC
SYSBUFFERDESCRIPTORS
SYSCOMMITTABLE
SYSCONNECTIONS
SYSCURSORCOLS
SYSCURSORREFS
SYSCURSORS
SYSCURSORTBLS
SYSDMEXECCACHEDPLANDEPENDENTOBJECTS
SYSDMEXECCACHEDPLANS
SYSDMEXECSESSIONWAITSTATS
SYSERRORS
SYSFULLTEXTLANGUAGES
SYSHOSTS
SYSINFO
SYSINPUTBUFFER
SYSIOS
SYSLANG
SYSLATCHSTATS
SYSLOCKINFO
SYSLOCKINFORMATION
SYSMEMALLOCS
SYSMEMCLERKS
SYSMEMORYCACHECLOCKHANDS
SYSMEMORYCACHECOUNTERS
SYSMEMORYCACHEENTRIES
SYSMEMORYCACHEHASHTABLES
SYSMEMORYOBJECTS
SYSMODULES
SYSNOTIFICATIONSUBS
SYSPROCESSES
SYSREF
SYSREQUESTS
SYSRINGBUFFERS
SYSRULESTATS
SYSSCHEDULERS
SYSSEMANTICLANGUAGES
SYSSESSIONS
SYSSTACKS
SYSTASKS
SYSTCPLISTENER
SYSTEM_REFERENCES
SYSTHREADS
SYSTRACECATEGORIES
SYSTRACECOLUMNS
SYSTRACEEVENTBINDINGS
SYSTRACEEVENTS
SYSTRACES
SYSTRACESUBCLASSVALUES
SYSVADUMP
SYSWAITS
SYSWAITSTATS
SYSWORKERS
SYSWORKERTLS
SYS_PERFORMANCE_COUNTERS
SYS_RG_EXTERNAL_POOL_AFFINITY
SYS_RG_POOL_AFFINITY
TAPE_STATUS
TEMPSTATS
TIME_ZONE_INFO
TRACEEVENTINFO
TRACEFILTERINFO
TRACEINFO
TRANSLATEPERMISSIONS
TRCDATA
TRCTABLE
TRIGGER_STATS
TRUSTED_ASSEMBLIES
USERROLES
VALIDATEPLANGUIDE
VALID_USE_HINTS
VIRTUALFILESTATS
VIRTUALSERVERNODES
WORDBREAK
XTP_BFC
XTP_CACHED_PLANS
XTP_CHECKPOINTS
XTP_CKPT
XTP_CKPTV2
XTP_CKPT_FILES
XTP_CKPT_FILESV2
XTP_EP_CRITICAL_REGIONS
XTP_GC_CYCLE_STATS
XTP_GC_QUEUE_STATS
XTP_HASH_IDX_STATS
XTP_INDEX_STATS
XTP_MERGE_REQUEST_TABLE
XTP_MM_CONSUMER
XTP_MM_TABLE_USAGE
XTP_PERFORMANCE_COUNTERS
XTP_PROCESS_LOG_REC
XTP_PROC_STATS
XTP_RANGE_INDEX_STATS
XTP_STMT_STATS
XTP_STORAGE
XTP_SYSTEM_GC_STATS
XTP_SYSTEM_STATS
XTP_TABLE_STATS
XTP_THREADS
XTP_TOMBSTONE
XTP_TRANSACTIONS
XTP_TRANSACTION_RECENT_ROWS
dm_tran_session_transactions
event_notification_event_types
persisted_sku_features

Best Answer

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 = <insert_object_id_of_procedure>

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.

Related Question