I just read about fn_dblog, and fn_dump_dblog. I was wondering how do I know what functions like this exist,
I'm not the only one either, the comments are scattered with people wanting to know more,
Thank you for your post about the fn_dump_dblog it was very interesting. This post makes me want to look through what other cool undocumented functions exist.
Note taken from sys.sysobjects (Transact-SQL)
This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Everything is documented in the master database, unless it is an extended stored procedure, in which case some information is stored in an external *.DLL file.
You might want to consider having a look at the sys.system_objects table, which contains a wealth of information:
SELECT * FROM sys.system_objects WHERE name LIKE '%fn_dblog%'
Of course, you could omit the search predicate and enjoy the information overflow of the 2072 objects found in a SQL Server 2014 instance:
select left(NAME,60), left(type_desc,35), is_ms_shipped from sys.system_objects
Showing the top 100 system_objects:
name type_desc is_ms_shipped ------------------------------------------------------------ ----------------------------------- ------------- sp_MSalreadyhavegeneration SQL_STORED_PROCEDURE 1 sp_MSwritemergeperfcounter SQL_STORED_PROCEDURE 1 TABLE_PRIVILEGES VIEW 1 sp_replsetsyncstatus EXTENDED_STORED_PROCEDURE 1 sp_replshowcmds SQL_STORED_PROCEDURE 1 sp_publishdb SQL_STORED_PROCEDURE 1 sp_addqueued_artinfo SQL_STORED_PROCEDURE 1 sp_replcounters EXTENDED_STORED_PROCEDURE 1 sp_MSget_subscription_dts_info SQL_STORED_PROCEDURE 1 sp_help_spatial_geometry_index_xml SQL_STORED_PROCEDURE 1 sp_password SQL_STORED_PROCEDURE 1 dm_resource_governor_resource_pool_volumes VIEW 1 sp_MSstopdistribution_agent SQL_STORED_PROCEDURE 1 sp_replmonitorrefreshjob SQL_STORED_PROCEDURE 1 sp_redirect_publisher SQL_STORED_PROCEDURE 1 sp_MSenumpartialchangesdirect SQL_STORED_PROCEDURE 1 sp_MSupdate_subscriber_info SQL_STORED_PROCEDURE 1 sp_MSdrop_distribution_agent SQL_STORED_PROCEDURE 1 sp_bindsession EXTENDED_STORED_PROCEDURE 1 sp_MSallocate_new_identity_range SQL_STORED_PROCEDURE 1 sp_MSenum_snapshot SQL_STORED_PROCEDURE 1 sp_replsendtoqueue EXTENDED_STORED_PROCEDURE 1 sp_dropmergepullsubscription SQL_STORED_PROCEDURE 1 sp_MShelpvalidationdate SQL_STORED_PROCEDURE 1 dm_os_hosts VIEW 1 sp_cycle_errorlog SQL_STORED_PROCEDURE 1 sp_MSgetdynamicsnapshotapplock SQL_STORED_PROCEDURE 1 sp_rename SQL_STORED_PROCEDURE 1 sp_dropremotelogin SQL_STORED_PROCEDURE 1 sp_addpullsubscription SQL_STORED_PROCEDURE 1 xp_instance_regread EXTENDED_STORED_PROCEDURE 1 sp_cdc_disable_table SQL_STORED_PROCEDURE 1 sp_is_makegeneration_needed SQL_STORED_PROCEDURE 1 sp_MSaddmergedynamicsnapshotjob SQL_STORED_PROCEDURE 1 sp_MSrepl_agentstatussummary SQL_STORED_PROCEDURE 1 sp_changelogreader_agent SQL_STORED_PROCEDURE 1 dm_os_memory_brokers VIEW 1 sp_MSdynamicsnapshotjobexistsatdistributor SQL_STORED_PROCEDURE 1 sp_procedure_params_100_rowset2 SQL_STORED_PROCEDURE 1 dm_db_stats_properties_internal SQL_INLINE_TABLE_VALUED_FUNCTION 1 sp_MSenum_merge_subscriptions SQL_STORED_PROCEDURE 1 sp_autostats SQL_STORED_PROCEDURE 1 fn_sqlvarbasetostr SQL_SCALAR_FUNCTION 1 sp_assemblies_rowset_rmt SQL_STORED_PROCEDURE 1 sp_columns_90_rowset_rmt SQL_STORED_PROCEDURE 1 sp_MScomputemergearticlescreationorder SQL_STORED_PROCEDURE 1 dm_exec_cached_plan_dependent_objects SQL_INLINE_TABLE_VALUED_FUNCTION 1 sp_addserver SQL_STORED_PROCEDURE 1 sp_MSchecksnapshotstatus SQL_STORED_PROCEDURE 1 sp_MShelp_distribution_agentid SQL_STORED_PROCEDURE 1 sp_MSaddlightweightmergearticle SQL_STORED_PROCEDURE 1 openkeys VIEW 1 sp_MSarticlecleanup SQL_STORED_PROCEDURE 1 sp_MSexecwithlsnoutput SQL_STORED_PROCEDURE 1 sp_new_parallel_nested_tran_id EXTENDED_STORED_PROCEDURE 1 dm_os_memory_allocations VIEW 1 sp_MShelp_identity_property SQL_STORED_PROCEDURE 1 sp_replicationdboption SQL_STORED_PROCEDURE 1 sp_MScreate_sub_tables SQL_STORED_PROCEDURE 1 sp_start_user_instance EXTENDED_STORED_PROCEDURE 1 sp_MSresetsnapshotdeliveryprogress SQL_STORED_PROCEDURE 1 sp_MSremove_mergereplcommand SQL_STORED_PROCEDURE 1 dm_db_xtp_nonclustered_index_stats VIEW 1 dm_db_mirroring_past_actions VIEW 1 dm_xe_session_object_columns VIEW 1 fn_cdc_get_column_ordinal SQL_SCALAR_FUNCTION 1 sp_MSadd_repl_command SQL_STORED_PROCEDURE 1 sp_MSgetagentoffloadinfo SQL_STORED_PROCEDURE 1 sp_MSgetconflicttablename SQL_STORED_PROCEDURE 1 sp_script_reconciliation_sinsproc SQL_STORED_PROCEDURE 1 sp_MSinsertgenerationschemachanges SQL_STORED_PROCEDURE 1 dm_os_loaded_modules VIEW 1 sp_MSgetlightweightmetadatabatch SQL_STORED_PROCEDURE 1 sp_MSNonSQLDDLForSchemaDDL SQL_STORED_PROCEDURE 1 fn_replreplacesinglequoteplusprotectstring SQL_SCALAR_FUNCTION 1 sp_dropextendedproc SQL_STORED_PROCEDURE 1 sp_MSdopartialdatabasesnapshotinitiation SQL_STORED_PROCEDURE 1 sp_help_fulltext_catalogs_cursor SQL_STORED_PROCEDURE 1 dm_db_task_space_usage VIEW 1 sp_MSadd_replcmds_mcit SQL_STORED_PROCEDURE 1 sp_changemergesubscription SQL_STORED_PROCEDURE 1 sp_MSgetonerow SQL_STORED_PROCEDURE 1 sp_MSquerysubtype SQL_STORED_PROCEDURE 1 sp_MSevaluate_change_membership_for_pubid SQL_STORED_PROCEDURE 1 sp_MSrepl_dropdatatypemappings SQL_STORED_PROCEDURE 1 sp_MSdetect_nonlogged_shutdown SQL_STORED_PROCEDURE 1 sp_change_log_shipping_primary_database SQL_STORED_PROCEDURE 1 fn_dump_dblog SQL_INLINE_TABLE_VALUED_FUNCTION 1 sp_MSrepl_gettype_mappings SQL_STORED_PROCEDURE 1 sp_MSenum_distribution SQL_STORED_PROCEDURE 1 sp_MSrepl_enumpublishertables SQL_STORED_PROCEDURE 1 sp_MSsetupbelongs SQL_STORED_PROCEDURE 1 dm_os_memory_objects VIEW 1 sp_columns_rowset_rmt SQL_STORED_PROCEDURE 1 sp_grantdbaccess SQL_STORED_PROCEDURE 1 sp_helpsort SQL_STORED_PROCEDURE 1 sp_MSscriptviewproc SQL_STORED_PROCEDURE 1 sp_distcounters SQL_STORED_PROCEDURE 1 sp_MSgettrancftsrcrow SQL_STORED_PROCEDURE 1 sp_MSactivatelogbasedarticleobject SQL_STORED_PROCEDURE 1
Note: The listing will contain both documented and undocumented objects.
I believe the only way to find these is to hunt. For starters, both of these functions are typed IF which is "In-lined table-function" you have
SELECT name FROM sys.sysobjects
WHERE type = 'IF' OR xtype = 'IF'
ORDER BY name;
However, they're certainly not all "undocumented". From that list, you'll have to check it against learn.microsoft.com. For completion here is the list as of SQL Server 2017 generated with find_undocumented.mjs
dm_db_database_page_allocations
dm_db_objects_disabled_on_compatibility_level_change
dm_db_stats_properties_internal
dm_logconsumer_cachebufferrefs
dm_logconsumer_privatecachebuffers
dm_logpool_consumers
dm_logpool_sharedcachebuffers
dm_logpoolmgr_freepools
dm_logpoolmgr_respoolsize
dm_logpoolmgr_stats
dm_os_enumerate_filesystem
dm_os_file_exists
fn_column_store_row_groups
fn_dblog
fn_dblog_xtp
fn_dump_dblog
fn_dump_dblog_xtp
fn_full_dblog