SQL Server – Script Statistics using SSMS

Background

This is another post that covers how to tackle issues when data in Production is a lot more that corresponding data in our Development environment.

In this post, we script out statistics from our originating environment.

Lineage

  1. SQL Server – Statistics – Fake Stats
    Link
  2. SQL Server – Clone Database – Schema & Statistics
    Link

 

SSMS

Identify Statistics

SQL

Syntax


exec sp_helpstats
         @objname = [object_name]
       , @results = 'ALL'

Sample

declare @objname sysname
declare @results varchar(30)

set @objname = '[Dimension].[Date]'
set @results = 'ALL'

exec sp_helpstats
          @objname = @objname
	, @results = @results

Output

sp_helpstats.20181117.0253PM

Script Objects

Steps

  1. Select Database
    • Right click on Database and from the drop-down menu choose Tasks \ Generate Scripts
    • The “Generate and Publish Scripts” Window appears
      • Tabs
        • Tab – “Choose Objects”
          • Please select Objects
        • Tab – “Scripting Options”
          • click on the Advanced button
          • Please ensure that the options listed below are checked
            • General
              • Check for Object Existence
                • True
              • Script Statistics
                • Script Statistics and Histograms
              • Type of data to script
                • Schema Only
            • Table and View Options
              • Script Check Constraints
                • True
              • Script Indexes
                • True
              • Script Primary Keys
                • True
              • Script Unique Keys
                • True

Images

Choose Objects

GenerateAndPublishScripts.Tab.ChooseObjects.2018117.0257PM.PNG

Set Scripting Options

GenerateAndPublishScripts.Tab.SetScriptingOptions.AdvancedScriptingOptions.2018117.0300PM.PNG

Summary

GenerateAndPublishScripts.Tab.Summary.2018117.0302PM.PNG

Save or Publish Scripts

GenerateAndPublishScripts.Tab.SaveOrPublishScripts.2018117.0304PM.PNG

SQL

Review generated SQL

Dimension.Date

Sample


if  exists (select * from sys.stats where name = N'PK_Dimension_Date' and object_id = object_id(N'[Dimension].[Date]'))
UPDATE STATISTICS [Dimension].[Date]([PK_Dimension_Date]) WITH STATS_STREAM = 0x01000000010000000000000000000000CDF5EE6F000000001C19000000000000DC18000000000000280300002800000003000A00000000000000000000000000070000004DE1CF001DA60000B505000000000000B5050000000000000000803F846D333A0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000C8000000C800000001000000130000000000404000A0B644000000000000404000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001100000000000000000000000000000070170000000000007817000000000000400600000000000056060000000000006C0600000000000082060000000000009806000000000000AE06000000000000C406000000000000DA06000000000000F00600000000000006070000000000001C07000000000000320700000000000048070000000000005E0700000000000074070000000000008A07000000000000A007000000000000B607000000000000CC07000000000000E207000000000000F8070000000000000E0800000000000024080000000000003A08000000000000500800000000000066080000000000007C080000000000009208000000000000A808000000000000BE08000000000000D408000000000000EA08000000000000000900000000000016090000000000002C09000000000000420900000000000058090000000000006E0900000000000084090000000000009A09000000000000B009000000000000C609000000000000DC09000000000000F209000000000000080A0000000000001E0A000000000000340A0000000000004A0A000000000000600A000000000000760A0000000000008C0A000000000000A20A000000000000B80A000000000000CE0A000000000000E40A000000000000FA0A000000000000100B000000000000260B0000000000003C0B000000000000520B000000000000680B0000000000007E0B000000000000940B000000000000AA0B000000000000C00B000000000000D60B000000000000EC0B000000000000020C000000000000180C0000000000002E0C000000000000440C0000000000005A0C000000000000700C000000000000860C0000000000009C0C000000000000B20C000000000000C80C000000000000DE0C000000000000F40C0000000000000A0D000000000000200D000000000000360D0000000000004C0D000000000000620D000000000000780D0000000000008E0D000000000000A40D000000000000BA0D000000000000D00D000000000000E60D000000000000FC0D000000000000120E000000000000280E0000000000003E0E000000000000540E0000000000006A0E000000000000800E000000000000960E000000000000AC0E000000000000C20E000000000000D80E000000000000EE0E000000000000040F0000000000001A0F000000000000300F000000000000460F0000000000005C0F000000000000720F000000000000880F0000000000009E0F000000000000B40F000000000000CA0F000000000000E00F000000000000F60F0000000000000C10000000000000221000000000000038100000000000004E1000000000000064100000000000007A100000000000009010000000000000A610000000000000BC10000000000000D210000000000000E810000000000000FE1000000000000014110000000000002A11000000000000401100000000000056110000000000006C1100000000000082110000000000009811000000000000AE11000000000000C411000000000000DA11000000000000F01100000000000006120000000000001C12000000000000321200000000000048120000000000005E1200000000000074120000000000008A12000000000000A012000000000000B612000000000000CC12000000000000E212000000000000F8120000000000000E1300000000000024130000000000003A13000000000000501300000000000066130000000000007C130000000000009213000000000000A813000000000000BE13000000000000D413000000000000EA13000000000000001400000000000016140000000000002C14000000000000421400000000000058140000000000006E1400000000000084140000000000009A14000000000000B014000000000000C614000000000000DC14000000000000F21400000000000008150000000000001E1500000000000034150000000000004A15000000000000601500000000000076150000000000008C15000000000000A215000000000000B815000000000000CE15000000000000E415000000000000FA15000000000000101600000000000026160000000000003C16000000000000521600000000000068160000000000007E160000000000009416000000000000AA16000000000000C016000000000000D616000000000000EC16000000000000021700000000000018170000000000002E1700000000000044170000000000005A17000000000000100013000000803F000000000000803F94360B040000100013000000803F000000400000803F97360B040000100013000000803F0000E0400000803F9F360B040000100013000000803F0000E0400000803FA7360B040000100013000000803F0000E0400000803FAF360B040000100013000000803F000040400000803FB3360B040000100013000000803F0000E0400000803FBB360B040000100013000000803F0000E0400000803FC3360B040000100013000000803F000040400000803FC7360B040000100013000000803F0000E0400000803FCF360B040000100013000000803F0000E0400000803FD7360B040000100013000000803F0000E0400000803FDF360B040000100013000000803F0000E0400000803FE7360B040000100013000000803F0000E0400000803FEF360B040000100013000000803F0000E0400000803FF7360B040000100013000000803F0000A0400000803FFD360B040000100013000000803F0000A0400000803F03370B040000100013000000803F0000E0400000803F0B370B040000100013000000803F000040400000803F0F370B040000100013000000803F0000E0400000803F17370B040000100013000000803F0000E0400000803F1F370B040000100013000000803F0000E0400000803F27370B040000100013000000803F0000A0400000803F2D370B040000100013000000803F0000A0400000803F33370B040000100013000000803F0000E0400000803F3B370B040000100013000000803F0000E0400000803F43370B040000100013000000803F0000E0400000803F4B370B040000100013000000803F000010410000803F55370B040000100013000000803F0000E0400000803F5D370B040000100013000000803F0000E0400000803F65370B040000100013000000803F0000E0400000803F6D370B040000100013000000803F0000E0400000803F75370B040000100013000000803F0000E0400000803F7D370B040000100013000000803F0000E0400000803F85370B040000100013000000803F0000A0400000803F8B370B040000100013000000803F0000E0400000803F93370B040000100013000000803F000040400000803F97370B040000100013000000803F0000A0400000803F9D370B040000100013000000803F000040400000803FA1370B040000100013000000803F0000A0400000803FA7370B040000100013000000803F0000E0400000803FAF370B040000100013000000803F000040400000803FB3370B040000100013000000803F0000E0400000803FBB370B040000100013000000803F0000E0400000803FC3370B040000100013000000803F0000A0400000803FC9370B040000100013000000803F0000E0400000803FD1370B040000100013000000803F0000A0400000803FD7370B040000100013000000803F000010410000803FE1370B040000100013000000803F0000E0400000803FE9370B040000100013000000803F000040400000803FED370B040000100013000000803F0000A0400000803FF3370B040000100013000000803F0000E0400000803FFB370B040000100013000000803F0000E0400000803F03380B040000100013000000803F0000E0400000803F0B380B040000100013000000803F000010410000803F15380B040000100013000000803F000010410000803F1F380B040000100013000000803F000010410000803F29380B040000100013000000803F0000E0400000803F31380B040000100013000000803F0000E0400000803F39380B040000100013000000803F0000E0400000803F41380B040000100013000000803F000040400000803F45380B040000100013000000803F0000E0400000803F4D380B040000100013000000803F000040400000803F51380B040000100013000000803F0000E0400000803F59380B040000100013000000803F000040400000803F5D380B040000100013000000803F0000E0400000803F65380B040000100013000000803F0000E0400000803F6D380B040000100013000000803F000040400000803F71380B040000100013000000803F0000E0400000803F79380B040000100013000000803F000040400000803F7D380B040000100013000000803F0000E0400000803F85380B040000100013000000803F0000E0400000803F8D380B040000100013000000803F000040400000803F91380B040000100013000000803F0000E0400000803F99380B040000100013000000803F0000E0400000803FA1380B040000100013000000803F0000E0400000803FA9380B040000100013000000803F0000E0400000803FB1380B040000100013000000803F0000E0400000803FB9380B040000100013000000803F0000E0400000803FC1380B040000100013000000803F000040400000803FC5380B040000100013000000803F0000E0400000803FCD380B040000100013000000803F0000E0400000803FD5380B040000100013000000803F0000E0400000803FDD380B040000100013000000803F0000E0400000803FE5380B040000100013000000803F0000E0400000803FED380B040000100013000000803F0000E0400000803FF5380B040000100013000000803F0000E0400000803FFD380B040000100013000000803F000040400000803F01390B040000100013000000803F0000E0400000803F09390B040000100013000000803F000040400000803F0D390B040000100013000000803F0000E0400000803F15390B040000100013000000803F0000E0400000803F1D390B040000100013000000803F0000E0400000803F25390B040000100013000000803F0000E0400000803F2D390B040000100013000000803F0000E0400000803F35390B040000100013000000803F0000E0400000803F3D390B040000100013000000803F000040400000803F41390B040000100013000000803F0000E0400000803F49390B040000100013000000803F000040400000803F4D390B040000100013000000803F0000E0400000803F55390B040000100013000000803F0000E0400000803F5D390B040000100013000000803F000040400000803F61390B040000100013000000803F0000E0400000803F69390B040000100013000000803F0000E0400000803F71390B040000100013000000803F000040400000803F75390B040000100013000000803F0000E0400000803F7D390B040000100013000000803F000040400000803F81390B040000100013000000803F0000E0400000803F89390B040000100013000000803F0000E0400000803F91390B040000100013000000803F0000A0400000803F97390B040000100013000000803F0000A0400000803F9D390B040000100013000000803F0000A0400000803FA3390B040000100013000000803F0000A0400000803FA9390B040000100013000000803F0000A0400000803FAF390B040000100013000000803F0000A0400000803FB5390B040000100013000000803F0000A0400000803FBB390B040000100013000000803F0000A0400000803FC1390B040000100013000000803F0000A0400000803FC7390B040000100013000000803F0000A0400000803FCD390B040000100013000000803F0000A0400000803FD3390B040000100013000000803F0000A0400000803FD9390B040000100013000000803F0000A0400000803FDF390B040000100013000000803F0000A0400000803FE5390B040000100013000000803F0000A0400000803FEB390B040000100013000000803F0000E0400000803FF3390B040000100013000000803F0000E0400000803FFB390B040000100013000000803F0000E0400000803F033A0B040000100013000000803F0000E0400000803F0B3A0B040000100013000000803F0000E0400000803F133A0B040000100013000000803F0000E0400000803F1B3A0B040000100013000000803F0000E0400000803F233A0B040000100013000000803F0000E0400000803F2B3A0B040000100013000000803F0000E0400000803F333A0B040000100013000000803F0000E0400000803F3B3A0B040000100013000000803F0000E0400000803F433A0B040000100013000000803F0000E0400000803F4B3A0B040000100013000000803F0000E0400000803F533A0B040000100013000000803F0000E0400000803F5B3A0B040000100013000000803F0000E0400000803F633A0B040000100013000000803F0000E0400000803F6B3A0B040000100013000000803F0000E0400000803F733A0B040000100013000000803F0000E0400000803F7B3A0B040000100013000000803F0000E0400000803F833A0B040000100013000000803F0000E0400000803F8B3A0B040000100013000000803F0000E0400000803F933A0B040000100013000000803F0000E0400000803F9B3A0B040000100013000000803F0000E0400000803FA33A0B040000100013000000803F0000E0400000803FAB3A0B040000100013000000803F0000E0400000803FB33A0B040000100013000000803F0000E0400000803FBB3A0B040000100013000000803F0000E0400000803FC33A0B040000100013000000803F0000E0400000803FCB3A0B040000100013000000803F0000E0400000803FD33A0B040000100013000000803F0000E0400000803FDB3A0B040000100013000000803F0000E0400000803FE33A0B040000100013000000803F0000E0400000803FEB3A0B040000100013000000803F0000E0400000803FF33A0B040000100013000000803F0000E0400000803FFB3A0B040000100013000000803F0000E0400000803F033B0B040000100013000000803F0000E0400000803F0B3B0B040000100013000000803F0000E0400000803F133B0B040000100013000000803F0000E0400000803F1B3B0B040000100013000000803F0000E0400000803F233B0B040000100013000000803F0000E0400000803F2B3B0B040000100013000000803F0000E0400000803F333B0B040000100013000000803F0000E0400000803F3B3B0B040000100013000000803F0000E0400000803F433B0B040000100013000000803F0000E0400000803F4B3B0B040000100013000000803F0000E0400000803F533B0B040000100013000000803F0000E0400000803F5B3B0B040000100013000000803F0000E0400000803F633B0B040000100013000000803F0000E0400000803F6B3B0B040000100013000000803F0000E0400000803F733B0B040000100013000000803F0000E0400000803F7B3B0B040000100013000000803F0000E0400000803F833B0B040000100013000000803F0000E0400000803F8B3B0B040000100013000000803F0000E0400000803F933B0B040000100013000000803F0000E0400000803F9B3B0B040000100013000000803F0000E0400000803FA33B0B040000100013000000803F0000E0400000803FAB3B0B040000100013000000803F0000E0400000803FB33B0B040000100013000000803F0000E0400000803FBB3B0B040000100013000000803F0000E0400000803FC33B0B040000100013000000803F0000E0400000803FCB3B0B040000100013000000803F0000E0400000803FD33B0B040000100013000000803F0000E0400000803FDB3B0B040000100013000000803F0000E0400000803FE33B0B040000100013000000803F0000E0400000803FEB3B0B040000100013000000803F0000E0400000803FF33B0B040000100013000000803F0000E0400000803FFB3B0B040000100013000000803F0000E0400000803F033C0B040000100013000000803F0000E0400000803F0B3C0B040000100013000000803F0000E0400000803F133C0B040000100013000000803F0000E0400000803F1B3C0B040000100013000000803F0000E0400000803F233C0B040000100013000000803F000010410000803F2D3C0B040000100013000000803F000010410000803F373C0B040000100013000000803F000010410000803F413C0B040000100013000000803F0000A0400000803F473C0B040000100013000000803F000000000000803F483C0B040000B505000000000000, ROWCOUNT = 1826, PAGECOUNT = 34
GO

/****** Object:  Statistic [statFiscalYear]    Script Date: 11/17/2018 3:04:50 PM ******/
if not exists (select * from sys.stats where name = N'statFiscalYear' and object_id = object_id(N'[Dimension].[Date]'))
CREATE STATISTICS [statFiscalYear] ON [Dimension].[Date]([Fiscal Year]) WITH STATS_STREAM = 0x01000000010000000000000000000000BCAE575B0000000066020000000000002602000000000000380300003800000004000A000000000000000000000000000700000000F6DE009BA900002207000000000000220700000000000000000000ABAA2A3E000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006000000060000000100000014000000000080400040E4440000000000008040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000011000000000000000000000000000000BA00000000000000C200000000000000300000000000000047000000000000005E0000000000000075000000000000008C00000000000000A3000000000000001000140000009843000000000000803FDC070000040000100014000080B643000000000000803FDD070000040000100014000080B643000000000000803FDE070000040000100014000080B643000000000000803FDF070000040000100014000000B743000000000000803FE00700000400001000140000007442000000000000803FE10700000400002207000000000000

Explanation

  1. Create Statistics
    • Does Statistics exist
    • If not
      • Create Statistics
        • Stats Stream
          • Binary Blob
        • Predicates
          • ROWCOUNT = 1826
          • PAGECOUNT = 34

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s