在没有权限运行 Exec sp_updatestats 时，用这个存储过程来更新数据库中所有表的统计信息

GO
/****** Object:  StoredProcedure [dbo].[mysp_updatestats]    Script Date: 2023/6/7 15:02:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 在没有权限运行 Exec sp_updatestats 时，用这个存储过程来更新数据库中所有表的统计信息
-- 当前采用的是 UPDATE STATISTICS [dbo].[mytablename]  WITH FULLSCAN 的方式
-- 作者：huang wanjing
-- 执行方式： exec [mysp_updatestats]
create procedure [dbo].[mysp_updatestats]

as

	-- required so it can update stats on ICC/IVs
	set ansi_warnings on
	set ansi_padding on
	set arithabort on
	set concat_null_yields_null on
	set numeric_roundabort off

	declare @exec_stmt_head nvarchar(4000)	-- "UPDATE STATISTICS [sysname].[sysname] "

	declare @updated_count int
	declare @skipped_count int

	declare @sch_id int
	declare @schema_name sysname
	declare @table_name sysname
	declare @table_id int
	declare @table_type char(2)
	declare @schema_table_name nvarchar(640) -- assuming sysname is 128 chars, 5x that, so it's > 128*4+4

	--declare @compatlvl tinyint

	declare ms_crs_tnames cursor local fast_forward read_only for
		select name, object_id, schema_id, type from sys.objects o
		where o.type = 'U' --### or o.type = 'IT'
	open ms_crs_tnames
	fetch next from ms_crs_tnames into @table_name, @table_id, @sch_id, @table_type


	while (@@fetch_status <> -1) -- fetch successful
	begin
		-- generate fully qualified quoted name
		select @schema_name = schema_name(@sch_id)
		select @schema_table_name = quotename(@schema_name, '[') +'.'+ quotename(rtrim(@table_name), '[')

		-- check for table with disabled clustered index
		if (1 = isnull((select is_disabled from sys.indexes where object_id = @table_id and index_id = 1), 0))
		begin
			-- raiserror('Table ''%s'': cannot perform the operation on the table because its clustered index is disabled', -1, -1, @tablename)
			raiserror(15654, -1, -1, @schema_table_name)
		end
		else
		begin
			--print @schema_table_name
			-- filter out local temp tables and Hekaton tables
			-- Note that OBJECTPROPERTY returns NULL on type="IT" tables, thus we only call it on type='U' tables
			if ((@@fetch_status <> -2) and 
			   (substring(@table_name, 1, 1) <> '#'))  -- temp tables
			   --and ((@table_type<>'U') or (0 = OBJECTPROPERTY(@table_id, 'TableIsInMemory'))))	-- Hekaton tables
			begin
				-- reset counters for this table
				select @updated_count = 0
				select @skipped_count = 0

				-- print status message
				--raiserror('Updating %s', -1, -1, @schema_table_name)
				--raiserror(15650, -1, -1, @schema_table_name)

				-- initial statement preparation: UPDATE STATISTICS [schema].[name]
				select @exec_stmt_head = 'UPDATE STATISTICS ' + @schema_table_name + '  WITH FULLSCAN'
				print @exec_stmt_head
				exec (@exec_stmt_head)
			end
		end
		print ' '
		fetch next from ms_crs_tnames into @table_name, @table_id, @sch_id, @table_type
	end
	raiserror(15005,-1,-1)
	deallocate ms_crs_tnames
	return(0) -- mysp_updatestats