如何在 Sql Server 中編寫統計腳本？（使用 T-SQL）

這裡有一個非常相似的問題：

為統計資訊創建 SQL 腳本

但這不是我想要的。

我想準確地取回我輸入的內容。例如：

CREATE STATISTICS [_MM_STATS__745366020_7_1_4_5_2_3] 
ON [dbo].[ProductShipTax]
([TaxRegionId], [ProductShipTaxID], [TaxRate], [ItemNo], [DateFrom], [DateTo])

我想要一個查詢，它可以讓我獲得上面的確切腳本，以便我可以例如將它應用到不同的數據庫。

這可能嗎？

下面是支持此語法子集的 2005+ 兼容選項。

CREATE STATISTICS statistics_name   
ON { table_or_indexed_view_name } ( column [ ,...n ] )   
   [ WHERE <filter_predicate> ]  
   [ WITH   
       [ STATS_STREAM = stats_stream ]   
       [ [ , ] NORECOMPUTE ]   
       [ [ , ] INCREMENTAL = { ON | OFF } ]  
   ] ; 

注意：但是，對 2005 的支持有些有限。該產品本身不支持增量或過濾統計資訊，並且我沒有引入對 stats 流的支持，因為在將此功能添加到CONVERT.

DECLARE @Schema             sysname,
       @Table              sysname,
       @StatsName          sysname,
       @IncludeStatsStream BIT,
       @StatsStream        VARCHAR(MAX),
       @TwoPartQuotedName  NVARCHAR(500);

select  @Schema           = 'dbo',
       @Table            = 'T1',
       @StatsName        = 'S1',
       @IncludeStatsStream = 0;

select @TwoPartQuotedName = QUOTENAME(@Schema) + '.' + QUOTENAME(@Table);


IF @IncludeStatsStream = 1 AND @@MICROSOFTVERSION/ POWER(2,24) > 9
 BEGIN
     DECLARE @StatsResults TABLE
       (
          StatsStream VARBINARY(MAX),
          Rows        BIGINT,
          DataPages   BIGINT
       );

     INSERT INTO @StatsResults
     EXEC sys.sp_executesql 
       N'DBCC SHOW_STATISTICS(@TwoPartQuotedName, @StatsName) WITH STATS_STREAM;',
       N'@TwoPartQuotedName NVARCHAR(500), @StatsName sysname',
       @TwoPartQuotedName = @TwoPartQuotedName,
       @StatsName = @StatsName;

     --Would need some other method on 2005 hence just skipping this
     SELECT @StatsStream = CONVERT(VARCHAR(MAX), StatsStream, 1)
     FROM   @StatsResults;
 END;

WITH stats AS
(
/* 
Support earlier versions without these columns using trick from http://dba.stackexchange.com/a/66755/3690 */
SELECT x.*
FROM (SELECT NULL AS filter_definition, NULL AS is_incremental) AS dummy
CROSS APPLY
(
 SELECT object_id, stats_id, name, no_recompute, filter_definition, is_incremental
 FROM sys.stats
) AS x
)
SELECT '
CREATE STATISTICS ' + QUOTENAME(name) + '   
ON ' + @TwoPartQuotedName + ' (' + SUBSTRING(cols, 2, 10000000) +')
'  + 
ISNULL(' WHERE ' + filter_definition,'') +
ISNULL(STUFF ( 
   ISNULL(',STATS_STREAM = ' + @StatsStream, '') +
   CASE WHEN no_recompute = 1 THEN ',NORECOMPUTE' ELSE '' END + 
   CASE WHEN is_incremental = 1 THEN ',INCREMENTAL=ON' ELSE '' END
, 1 , 1 ,  ' WITH '  ) , '') AS [processing-instruction(x)]
FROM   stats s
      CROSS APPLY (SELECT ',' + QUOTENAME(c.name)
                   FROM   sys.stats_columns sc
                          JOIN sys.columns c
                            ON c.object_id = sc.object_id
                               AND c.column_id = sc.column_id
                   WHERE  sc.object_id = s.object_id
                          AND sc.stats_id = s.stats_id
                   ORDER  BY sc.stats_column_id
                   FOR XML PATH(''))CA(cols)
WHERE  s.object_id = OBJECT_ID(@TwoPartQuotedName)
      AND s.name = @StatsName
FOR XML PATH('');

引用自：https://dba.stackexchange.com/questions/151136