Thursday, March 22, 2012

Scripting out statistics

I recently read an excellent post by Kevin Kline regarding scripting out histograms from one database and applying them to another.  What would have made it better is if there was a way to automate it.  And so I pulled out powershell and got to work.  Here's what I came up with:


$opts = new-object Microsoft.SqlServer.Management.SMO.ScriptingOptions;
$opts.OptimizerData = $true;

$server = new-object Microsoft.SqlServer.Management.SMO.Server ".";
$database = $server.Databases["AdventureWorks2008R2"];

foreach ($table in $database.Tables) {
    foreach ($stat in $table.Statistics) {
        $stat.Script($opts);
    }
}
 
 
You'll probably want to direct that to a file (either by setting the scripting options appropriately or redirecting the output from the command line), but there you go. If you're feeling frisky, you schedule that to happen every so often and ship it to your dev environment where it gets applied.  Also, if you want to do it for all the databases in your production environment, change the line that sets $database to get all the databases into a variable and add one more foreach loop around the ones that are already there to loop over them.

No comments:

Post a Comment