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], DROP_SCRIPT=N'DROP STATISTICS [' +ssc.name + ']' + '.[' + obj.name + ']' +'.['+s.name + '];', CREATE_SCRIPT='CREATE STATISTICS STATS_' + STUFF( ( SELECT ',' + (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, '') + '' + ' 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] INNER JOIN sys.schemas AS ssc ON obj.schema_id=ssc.schema_id
WHERE 1=1 AND obj.[name] ='ClientsDB' AND OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 --AND s.name='STAT_AdayLogDB_PostingDateN_PostingUser_PostingDateN' --AND (s.auto_created = 1 OR s.user_created = 1) --and s.name ='_MM_STATS__745366020_radhe'
