How to copy table statistics in sql server?
I am using SQL Server 2008R2.
I have a table A
that has a set of indexes and has statistics on those indexes. There A
are no triggers on the table .
I want to have a new table B
that is an exact copy of the table A
. This means that the table definition, indexes, statistics, and data in both tables are the same.
So when I ask A
, or B
, with the same results.
And when I ask for sys.indexes
, sys.dm_db_index_physical_stats
, sys.dm_db_index_usage_stats
and sys.dm_db_index_operational_stats
for A
, or B
the results are the same.
I mean the column object_id
will obviously be different, but I want:
- columns like
avg_fragmentation_in_percent
etc. asys.dm_db_index_physical_stats
they are the same - columns like
user_seeks
etc. asys.dm_db_index_usage_stats
they are the same - columns like
leaf_insert_count
etc. insys.dm_db_index_operational_stats
, are the same.
I can script define a table and its table indices A
and then rename them for the table B
.
But how does the script output the table statistics A
and copy them for the table B
?
source to share
Powershell:
pushd;
import-module sqlps -disablenamechecking;
popd;
$opts = new-object Microsoft.SqlServer.Management.SMO.ScriptingOptions;
$opts.OptimizerData = $true;
$server = new-object Microsoft.SqlServer.Management.SMO.Server '.';
$database = $server.Databases['AdventureWorks2014'];
$table = $database.tables | where {$_.schema -eq 'Person' -and $_.name -eq 'Person'}
foreach ($stat in $table.Statistics) {
$stat.Script($opts);
}
source to share