If you’ve ever been curios about ways to view SQL Server system objects’ definitions, to see what your favorite system objects do behind the scenes then this blog post is for you.
There are three methods I tend to resort to when it comes to viewing SQL Server system objects’ definitions.
Using OBJECT_DEFINITION
OBJECT_DEFINITION is a metadata function that returns the definition of a specified object ID.
This usage is pretty straightforward, you pass an object ID to OBJECT_DEFINITION and it returns the object definition.
A quick way to do that is with this small piece of T-SQL:
| 1 2 3 4 5 6 | USE [master] GO DECLARE @ObjectName NVARCHAR(257) = N'sys.sql_logins', @ObjectDef NVARCHAR(MAX); SELECT @ObjectDef = OBJECT_DEFINITION(OBJECT_ID(@ObjectName)); PRINT @ObjectDef; |
And the output for the above example using sys.sql_logins looks like this:
Querying sys.system_sql_modules
sys.system_sql_modules, as per the documentation, returns one row per system object that contains a SQL language-defined module. System objects of type FN, IF, P, PC, TF, V have an associated SQL module.
The following query can be used to retrieve the system objects definitions:
| 1 2 3 4 5 6 7 8 9 10 11 | USE [master] GO SELECT OBJECT_SCHEMA_NAME([m].[object_id]) + N'.' + [o].[name] AS [name], [o].[type], [m].[definition], [m].[uses_ansi_nulls], [m].[uses_quoted_identifier] FROM sys.[system_sql_modules] AS [m] INNER JOIN sys.[system_objects] AS [o] ON [m].[object_id] = [o].[object_id]; |
Using the Resource database
The Resource database is a read-only, system-only database. It holds SQL Server’s physical system objects, which logically appear in the sys schema of every database. It doesn’t store user data or metadata.
The files of the resource database live in the instance’s Binn directory
| 1 2 | $BinnPath = "C:\Program Files\Microsoft SQL Server\MSSQL15.VSQL2019\MSSQL\Binn\" Get-ChildItem $BinnPath | Where-Object Name -Like "mssqlsystemresource*" |
To access it any time I want, I’ll need to:
Copy the two files to the paths where my user database files live.
| 1 2 3 | $BinnPath = "C:\Program Files\Microsoft SQL Server\MSSQL15.VSQL2019\MSSQL\Binn\" Copy-Item "$BinnPath\mssqlsystemresource.mdf" -Destination "E:\VSQL\Data\" Copy-Item "$BinnPath\mssqlsystemresource.ldf" -Destination "E:\VSQL\TLog\" |
Note: I don’t need to worry about granting permissions on the files to the SQL Server service account, because they’ll be automatically inherited from the Data and TLog directories.
But if you run into issues with SQL Server accessing database and/or backup files, this post will help explain why.
Attach the files as a new user database
| 1 2 3 4 5 6 7 | USE [master] GO CREATE DATABASE [_mssqlsystemresource] ON ( FILENAME = N'E:\VSQL\Data\mssqlsystemresource.mdf' ), ( FILENAME = N'E:\VSQL\TLog\mssqlsystemresource.ldf' ) FOR ATTACH GO |
Digging through the Resource database
Afterwards you can use Object Explorer to browse through system objects like they were normal user objects.
And you can use right click->Modify to script out SQL Server system objects’ definitions.
Conclusion
Have fun digging through SQL Server system objects’ definitions.
Also, if you’re self-conscious about the way you format your T-SQL, you’ll soon see that Microsoft devs can be as sloppy with their formatting as the rest of us.
If you liked this post, you might also like my post about system object changes in SQL Server 2025 vs 2022.