Generate Create statements for Statstics in SQL Server


 

Howdy EveryOne

if you want to if exists delete and create scripts for the statstics in the SQL Server, the below code works fine.

declare @SQ VARCHAR(4)
SET @SQ = ''''
;WITH StatNames
AS
(
SELECT  s.name StatName, T.name as TableName,s.stats_id stats_id, T.object_id [object_id]
FROM    sys.stats   S   WITH (NOLOCK)
JOIN    sys.Tables  T   WITH (NOLOCK)
ON      S.object_id     = T.object_id
AND     S.user_created  = 1
),
Deflat
AS
(
SELECT SN.StatName,SN.TableName,STUFF(
(  select  ',' + C.name
FROM    sys.stats_columns SC   WITH (NOLOCK)
JOIN    sys.columns C WITH (NOLOCK)
ON      SC.column_id = C.column_id
AND     C.object_id = SN.object_id
AND     SC.stats_id = SN.stats_id
AND     C.object_id = SC.object_id
ORDER   BY SC.stats_column_id
FOR     XML PATH ('')
),1,1,'') Cols
FROM  StatNames SN
)
SELECT 'IF EXISTS ( SELECT 1 FROM sys.stats S, sys.tables T WHERE S.name = ' + @SQ +  StatName + @SQ + ' AND T.name = ' + @SQ + TableName + @SQ + ' ) '
+ ' DROP STATISTICS ' + TableName +'.'+StatName
+ ' CREATE  STATISTICS ' + StatName  + ' ON ' + TableName + '(' + Cols + ')'
FROM Deflat

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s