Wednesday, March 7, 2012

Re-engineering CREATE STATISTICS

Hello Folks,

I have a SQL Server 2000 database that has a number of named statitics on it. I unfortunately do not have the actual CREATE STATISTICS that were run against this database. I have been able to find these by using this query:

SELECT DISTINCT
object_name(i.id) as TableName
, i.Name as StatName
, c.name as ColName
FROM SysIndexes i
inner join SysIndexKeys k
on i.ID = k.id
inner join SysColumns c
on k.id = c.id and c.colid = k.colid
WHERE IndexProperty(i.id, i.name, 'IsStatistics') = 1
AND OBJECTPROPERTY (i.id, 'IsMsShipped') = 0
and i.name not like '_WA%'
order by i.name

My question is: Is there a way to reverse engineer or see the keywords that were used to create the statitics. I'm interested in this portion of the CREATE:

WITH
[ [ FULLSCAN
| SAMPLE number { PERCENT |

ROWS } ] [ , ] ]
[ NORECOMPUTE ]

Thanks, MarkAny ideas?

No comments:

Post a Comment