How to script statistics in Sql Server? (using T-SQL)
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('');
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] );