Dynamic script to script out statistics before dropping them

  • I am looking to script out statistics using the below filter before dropping them so that I can recreate later. I know we can do that by Generating Scripts from SSMS but since I have to look for specific stats only I can't do it that way. I tried a few things to loop in cursor but it's not working for me. Any help would be greatly appreciated


    SELECT * FROM sys.stats
    WHERE OBJECT_ID = OBJECT_ID('fctext_FB')
    AND name LIKE '%FB_ID'

  • I'm not sure exactly what you are trying to do. But I think you want a script like this:
    IF OBJECT_ID('dbo.sysStatsBak') IS NULL BEGIN
          SELECT TOP(0) *
            INTO dbo.sysStatsBak
            FROM sys.stats
    END

    INSERT INTO dbo.sysStatsBak
    SELECT *
      FROM sys.stats
     WHERE OBJECT_ID = OBJECT_ID('fctext_FB')
       AND name LIKE '%FB_ID'

  • Jonathan AC Roberts - Thursday, March 21, 2019 12:41 PM

    I'm not sure exactly what you are trying to do. But I think you want a script like this:
    IF OBJECT_ID('dbo.sysStatsBak') IS NULL BEGIN
          SELECT TOP(0) *
            INTO dbo.sysStatsBak
            FROM sys.stats
    END

    INSERT INTO dbo.sysStatsBak
    SELECT *
      FROM sys.stats
     WHERE OBJECT_ID = OBJECT_ID('fctext_FB')
       AND name LIKE '%FB_ID'

    No that is not what I am after. I think I was clear in what I wanted but I'll re-iterate. I am looking for dynamically creating the statistics definition by using the query I posted so basically when I run the script it should give me output to create only the stats based on the above query predicate...like a complete create stats script definition.

  • a post here has exactly what you are after, i think:
    https://dba.stackexchange.com/questions/151136/how-to-script-statistics-in-sql-server-using-t-sql

    just append your WHERE statement tot eh results to limit it to a single table:
    -----------------------
    -- 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'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell - Friday, March 22, 2019 6:27 AM

    a post here has exactly what you are after, i think:
    https://dba.stackexchange.com/questions/151136/how-to-script-statistics-in-sql-server-using-t-sql

    just append your WHERE statement tot eh results to limit it to a single table:
    -----------------------
    -- 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'

    Perfect...that is what I was looking for. Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply