Here is a very interesting question, I have received after reading yesterday’s blog post SQL SERVER – Statistics for Read-Only Database are in TempDB.
I like your blog post. I recently tried to create statistics with the help of SSMS but I was not successful. Everytime when I go to the option to script the statistics, it is always disabled. I initially thought this was because of the read-only database, however, when I tried to research more, I realized that this is not because of the Read-Only but this option is disabled across all the database irrespective of the database status.
See attached image? Do you know is there is any way to copy statistics from the server?”
Very interesting question – the matter of the fact, I have even noticed this behavior earlier but never wrote about it. Here is the answer.
We can’t create statistics with the help of SSMS CREATE script menu as displayed in the image above. If you want to copy or create statistics from any table or view you will have to use different methods in SSMS. You will have to Right Click on the Database and Script the Table/View with Statistics option enabled. Here is the blog post where I have explained this in detail.
Refer this blog post for the solution: SQL SERVER – Copy Statistics from One Server to Another Server
Here is the option which one has to enable scripting the table with Statistics details:
Do let me know if there is any other way to enable the scripting statistics. I will be glad to post the solution with due credit.
Reference: Pinal Dave (http://blog.SQLAuthority.com)