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
ENDINSERT 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_idWHERE sc.[object_id] = s.[object_id]
AND sc.stats_id = s.stats_idORDER 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