This UDF returns the threshold for all statistics when these statistics are automatically updated. Parameters: @object_schema: NULL or name of the object schema @object_name: NULL or the name of the table object @tf2371_enabled: 0 or 1 for new threshold calculation before SQL 2016

get_statistics_update_info.sql

CREATE OR ALTER FUNCTION dbo.statistics_update
(
    @object_id      INT,
    @stats_id       INT,
    @tf2371_enabled BIT = 1
)
RETURNS TABLE
AS
RETURN
(
	SELECT	s.name						AS	schema_name,
		t.name						AS	table_name,
		st.name						AS	statistics_name,
		st.no_recompute					AS	no_recompute,
		CASE WHEN db.is_auto_update_stats_on = 1
			THEN
			CASE WHEN st.no_recompute = 1
				THEN 'no automatic update'
				ELSE 'updated automatic '
		END +
		CASE WHEN db.is_auto_update_stats_async_on = 1
			THEN CASE WHEN st.no_recompute = 0
				THEN 'asyncronous'
				ELSE 'synchronous'
			     END
		ELSE ''
		END
			ELSE 'no automatic update'
		END						AS	automatic_update_status,
		dsp.stats_id,
		dsp.last_updated,
		dsp.rows,
		dsp.rows_sampled,
		dsp.steps,
		dsp.unfiltered_rows,
		dsp.modification_counter,
		CAST
		(
			/* The functionality has been implemented */
			CASE WHEN
				(
					db.sql_server_version < 13
					OR db.compatibility_level < 130
				) AND @tf2371_enabled = 0
				 THEN 500 + (0.20 * dsp.rows)
				 ELSE CASE WHEN 500 + (0.20 * dsp.rows) < SQRT(1000 * dsp.rows)
						   THEN 500 + (0.20 * dsp.rows)
						   ELSE SQRT(1000 * dsp.rows)
					  END
			END AS BIGINT
		)								AS	required_update_rows,
		FORMAT
		(
			dsp.modification_counter /
			CASE WHEN
				(
					db.sql_server_version < 13
					OR db.compatibility_level < 130
				) AND @tf2371_enabled = 0
				THEN 500 + (0.20 * dsp.rows)
				ELSE CASE WHEN 500 + (0.20 * dsp.rows) < SQRT(1000 * dsp.rows)
						THEN 500 + (0.20 * dsp.rows)
						ELSE SQRT(1000 * dsp.rows)
					END
			END,
			'#0.00%'
		)			AS update_counter_percentage
FROM	sys.schemas AS s INNER JOIN sys.tables AS t
	ON (s.schema_id = t.schema_id)
	INNER JOIN sys.stats AS st
	ON (t.object_id = st.object_id)
	CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS dsp
	CROSS APPLY
	(
		SELECT	CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) AS sql_server_version,
				compatibility_level,
				is_auto_update_stats_on,
				is_auto_update_stats_async_on
		FROM	sys.databases
		WHERE	name = DB_NAME()
	) AS db
WHERE	(
		@object_id IS NULL
		OR st.object_id = @object_id
	)
	AND
	(
		@stats_id IS NULL
		OR st.stats_id = @stats_id
	)
);
GO