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

Question

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?

Answer 1

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 <filter_predicate> ]  
    [ 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('');

Answer 2

If you are familiar with PowerShell, this can be done in two lines code (or even one line if we want to)

#function: on local instance, scripting out [AdventureWorks2012] db auto-created statistics (those stats for indexes are created with the indexes, so no worry needed)
#the generated file is put at c:\temp\stats_auto.sql

import-module sqlps -DisableNameChecking;

foreach ($t in (dir sqlserver:\sql\localhost\default\databases\AdventureWorks2012\Tables))
{
    $t.Statistics | ? { $_.IsAutoCreated} | % { "create statistics $($_.name) on [$($t.schema)].[$($t.name)] ( $($_.statisticColumns -join ',') )" } | Out-File c:\temp\stats_auto.sql -append; 
}

After running this script in a PowerShell IDE, I can get the following t-sql code in c:\temp\stats_auto.sql

create statistics _WA_Sys_00000002_24B26D99 on [dbo].[tblPerson] ( [NameStyle] );
create statistics _WA_Sys_00000005_49C3F6B7 on [HumanResources].[Employee] ( [OrganizationLevel] );
create statistics _WA_Sys_00000006_49C3F6B7 on [HumanResources].[Employee] ( [JobTitle] );
create statistics _WA_Sys_00000003_693CA210 on [Person].[Person] ( [NameStyle] );
create statistics _WA_Sys_00000003_71D1E811 on [Person].[PersonPhone] ( [PhoneNumberTypeID] );
create statistics _WA_Sys_00000003_1B9317B3 on [Person].[StateProvince] ( [CountryRegionCode] );
create statistics _WA_Sys_00000006_1B9317B3 on [Person].[StateProvince] ( [TerritoryID] );
create statistics _WA_Sys_00000003_1DE57479 on [Production].[BillOfMaterials] ( [ComponentID] );
create statistics _WA_Sys_00000004_1DE57479 on [Production].[BillOfMaterials] ( [StartDate] );
create statistics _WA_Sys_00000005_1DE57479 on [Production].[BillOfMaterials] ( [EndDate] );
create statistics _WA_Sys_00000002_403A8C7D on [Production].[Document] ( [DocumentLevel] );
create statistics _WA_Sys_00000004_403A8C7D on [Production].[Document] ( [Owner] );
create statistics _WA_Sys_0000000C_75A278F5 on [Production].[Product] ( [SizeUnitMeasureCode] );
create statistics _WA_Sys_0000000D_75A278F5 on [Production].[Product] ( [WeightUnitMeasureCode] );
create statistics _WA_Sys_00000013_75A278F5 on [Production].[Product] ( [ProductSubcategoryID] );
create statistics _WA_Sys_00000014_75A278F5 on [Production].[Product] ( [ProductModelID] );
create statistics _WA_Sys_00000002_0D7A0286 on [Production].[ProductDocument] ( [DocumentNode] );
create statistics _WA_Sys_00000002_0F624AF8 on [Production].[ProductInventory] ( [LocationID] );
create statistics _WA_Sys_00000002_1BC821DD on [Production].[ProductModelIllustration] ( [IllustrationID] );
create statistics _WA_Sys_00000002_1DB06A4F on [Production].[ProductModelProductDescriptionCulture] ( [ProductDescriptionID] );
create statistics _WA_Sys_00000003_1DB06A4F on [Production].[ProductModelProductDescriptionCulture] ( [CultureID] );
create statistics _WA_Sys_00000002_2180FB33 on [Production].[ProductProductPhoto] ( [ProductPhotoID] );
create statistics _WA_Sys_00000002_282DF8C2 on [Production].[ProductSubcategory] ( [ProductCategoryID] );
create statistics _WA_Sys_00000004_373B3228 on [Production].[WorkOrderRouting] ( [LocationID] );
create statistics _WA_Sys_00000006_3864608B on [Purchasing].[PurchaseOrderHeader] ( [ShipMethodID] );
create statistics _WA_Sys_00000003_398D8EEE on [Sales].[CurrencyRate] ( [FromCurrencyCode] );
create statistics _WA_Sys_00000004_398D8EEE on [Sales].[CurrencyRate] ( [ToCurrencyCode] );
create statistics _WA_Sys_00000002_3B75D760 on [Sales].[Customer] ( [PersonID] );
create statistics _WA_Sys_00000003_3B75D760 on [Sales].[Customer] ( [StoreID] );
create statistics _WA_Sys_00000005_3B75D760 on [Sales].[Customer] ( [AccountNumber] );
create statistics _WA_Sys_00000002_6FE99F9F on [Sales].[PersonCreditCard] ( [CreditCardID] );
create statistics _WA_Sys_00000006_44CA3770 on [Sales].[SalesOrderDetail] ( [SpecialOfferID] );
create statistics _WA_Sys_00000008_4B7734FF on [Sales].[SalesOrderHeader] ( [SalesOrderNumber] );
create statistics _WA_Sys_0000000D_4B7734FF on [Sales].[SalesOrderHeader] ( [TerritoryID] );
create statistics _WA_Sys_0000000E_4B7734FF on [Sales].[SalesOrderHeader] ( [BillToAddressID] );
create statistics _WA_Sys_0000000F_4B7734FF on [Sales].[SalesOrderHeader] ( [ShipToAddressID] );
create statistics _WA_Sys_00000010_4B7734FF on [Sales].[SalesOrderHeader] ( [ShipMethodID] );
create statistics _WA_Sys_00000011_4B7734FF on [Sales].[SalesOrderHeader] ( [CreditCardID] );
create statistics _WA_Sys_00000013_4B7734FF on [Sales].[SalesOrderHeader] ( [CurrencyRateID] );
create statistics _WA_Sys_00000002_5AB9788F on [Sales].[SalesOrderHeaderSalesReason] ( [SalesReasonID] );
create statistics _WA_Sys_00000002_5CA1C101 on [Sales].[SalesPerson] ( [TerritoryID] );
create statistics _WA_Sys_00000003_72910220 on [Sales].[SalesTerritory] ( [CountryRegionCode] );
create statistics _WA_Sys_00000002_7D0E9093 on [Sales].[SalesTerritoryHistory] ( [TerritoryID] );
create statistics _WA_Sys_00000004_0B5CAFEA on [Sales].[ShoppingCartItem] ( [ProductID] );

Answer 3

this script:

-- Details About Statistics
-- Original Author: Pinal Dave 
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s 

INNER JOIN sys.stats_columns sc 
        ON sc.[object_id] = s.[object_id] 
       AND sc.stats_id = s.stats_id

INNER JOIN sys.columns c 
        ON c.[object_id] = sc.[object_id] 
       AND c.column_id = sc.column_id

INNER JOIN sys.partitions par 
        ON par.[object_id] = s.[object_id]

INNER JOIN sys.objects obj 
        ON par.[object_id] = obj.[object_id]

WHERE 1=1
AND OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
and s.name ='_MM_STATS__745366020_7_1_4_5_2_3'

gives me this:

and from there:

the_script:

drop statistics 
[dbo].[ProductShipTax].[_MM_STATS__745366020_radhe] 
go

CREATE STATISTICS [_MM_STATS__745366020_radhe] 
ON [dbo].[ProductShipTax]( [TaxRegionId], [ProductShipTaxID], [TaxRate], [ItemNo], [DateFrom], [DateTo]) 
WHERE ([taxregionid]=(6))
WITH NORECOMPUTE,FULLSCAN

is retrieved:

CREATE STATISTICS [_MM_STATS__745366020_radhe] ON [dbo].[ProductShipTax]( [TaxRegionId], [ProductShipTaxID], [TaxRate], [ItemNo], [DateFrom], [DateTo]) 
WHERE ([taxregionid]=(6)) WITH NORECOMPUTE

using the script below:

-----------------------
-- script stats
-- not compatible with sql 2005
-- does not include STATS_STREAM
-- marcello miorelli
-- 30-sep-2016
-----------------------

SELECT DISTINCT
SCHEMA_NAME(obj.schema_id) as [Schema],
obj.[name]  AS TableName,
s.name AS StatName,
s.stats_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
s.auto_created,
s.user_created,
s.no_recompute,
s.is_incremental,
s.is_temporary,
s.filter_definition, -- not compatible with sql 2005
s.[object_id],

THE_SCRIPT='CREATE STATISTICS ' + QUOTENAME(S.NAME) + 
           ' ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + 
           '.' + QUOTENAME(obj.[name]) + 
           '(' +
            STUFF( ( SELECT ', ' + 
                           QUOTENAME(c.name)

                          FROM sys.stats_columns sc 

                    INNER 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('')),1 ,1, '') +
          ')' +
        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 '  ) , '')

FROM sys.stats s 

INNER JOIN sys.partitions par 
        ON par.[object_id] = s.[object_id]

INNER JOIN sys.objects obj 
        ON par.[object_id] = obj.[object_id]

WHERE 1=1
AND OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
--and s.name ='_MM_STATS__745366020_radhe'