March 21, 2019 at 12:00 pm
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'
March 21, 2019 at 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'
March 21, 2019 at 2:08 pm
Jonathan AC Roberts - Thursday, March 21, 2019 12:41 PMI'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.
March 22, 2019 at 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'
Lowell
March 22, 2019 at 10:09 am
Lowell - Friday, March 22, 2019 6:27 AMa 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-sqljust 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy