7

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?

7

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('');
| improve this 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] );
| improve this answer | |
1

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:

enter image description here

and from there:

enter image description here

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'
| improve this answer | |
  • 4
    Interesting. Some notes: 1) You can replace OBJECT_NAME(s.[object_id]) with obj.[name] since you are already joining to the sys.objects catalog view. 2) For the sake of completeness (for others), you should include the filter_definition column so that this script properly handles filtered statistics :-). – Solomon Rutzky Sep 30 '16 at 15:44
  • I have changed he script now. I also like this script because it does not use the DBCC SHOW_STATISTICS – Marcello Miorelli Oct 3 '16 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.