How to script statistics in Sql Server? (using T-SQL)

there is a very similar question here:

Create SQL Script for Statistics

But that is not what I want.

I want to get back exactly what I have put in.
For example:

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

I would like a query that would allow me to get the exact script above so that I could for example, apply it to a different database.

would this be possible?

A 2005+ compatible option that supports this subset of the grammar is below.

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

NB: Support for 2005 is somewhat limited however. The product itself doesn't support incremental or filtered statistics and I haven't introduced support for stats stream as it would need one of the kludgy workarounds to convert binary to string before this functionality was added to 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('');

StackExchange Database Administrators Q#151136, answer score: 8