script to manually create stats

createstats.sql

create proc CreateStats @table varchar(128) as
begin


	declare   @create_type    tinyint -- 1 default, 2 Fullscan, 3 Sample
	declare  @sample_pct     tinyint


	IF @create_type IS NULL
	BEGIN
		SET @create_type = 2;
	END;

	IF @create_type NOT IN (1,2,3)
	BEGIN
		THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
	END;

	IF @sample_pct IS NULL
	BEGIN;
		SET @sample_pct = 100;
	END;

	IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
	BEGIN;
		DROP TABLE #stats_ddl;
	END;

	IF OBJECT_ID('tempdb..#stats_ddl_temp') IS NOT NULL
	BEGIN;
		DROP TABLE #stats_ddl_temp;
	END;


	create table #stats_ddl_temp 
	(
		table_name varchar(128),
		table_schema_name   varchar(128),
		column_name  varchar(128),
		column_id bigint,
		object_id bigint,
		seq_nmbr bigint
	)

	insert into #stats_ddl_temp  
	SELECT      convert(varchar(128),t.[name])                         AS [table_name]
	,           convert(varchar(128),s.[name])                        AS [table_schema_name]
	,           convert(varchar(128),c.[name])                        AS [column_name]
	,           c.[column_id]                   AS [column_id]
	,           t.[object_id]                   AS [object_id]
	,           ROW_NUMBER()
				OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
	FROM        sys.[tables] t
	JOIN        sys.[schemas] s         ON  t.[schema_id]       = s.[schema_id]
	JOIN        sys.[columns] c         ON  t.[object_id]       = c.[object_id]
	LEFT JOIN   sys.[stats_columns] l   ON  l.[object_id]       = c.[object_id]
										AND l.[column_id]       = c.[column_id]
										AND l.[stats_column_id] = 1
	LEFT JOIN    sys.[external_tables] e    ON    e.[object_id]        = t.[object_id]
	WHERE       l.[object_id] IS NULL
	AND            e.[object_id] IS NULL -- not an external table


	create table #stats_ddl
	(
		table_name varchar(128),
		table_schema_name   varchar(128),
		column_name  varchar(128),
		column_id bigint,
		object_id bigint,
		seq_nmbr bigint,
		create_stat_ddl  varchar(max)
	)

	insert into #stats_ddl
	SELECT  [table_schema_name]
	,       [table_name]
	,       [column_name]
	,       [column_id]
	,       [object_id]
	,       [seq_nmbr]
	,       CASE @create_type
			WHEN 1
			THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
			WHEN 2
			THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
			WHEN 3
			THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
			END AS create_stat_ddl 
			from #stats_ddl_temp
where table_name like @table
	;

	select * from  #stats_ddl

	DECLARE @i INT              = 1
	,       @t INT              = (SELECT max(column_id) FROM #stats_ddl)
	,       @s NVARCHAR(4000)   = N''
	;


	WHILE @i <= @t
	BEGIN
		
		SELECT @s = create_stat_ddl FROM #stats_ddl WHERE column_id = @i;
		SELECT  create_stat_ddl FROM #stats_ddl WHERE column_id = @i;
		
		BEGIN TRY
			PRINT 'Executing- ' + @s
			EXEC sp_executesql @s
		END TRY
		BEGIN CATCH
			PRINT 'An error occurred: ' + ERROR_MESSAGE();
		END CATCH
		SET @i+=1;
	END

	IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
	BEGIN;
		DROP TABLE #stats_ddl;
	END;

	IF OBJECT_ID('tempdb..#stats_ddl_temp') IS NOT NULL
	BEGIN;
		DROP TABLE #stats_ddl_temp;
	END;

end