How to analyze all tables in one schema (edited)

[Julie]

Hello,

Is there a way I can analyze all the tables in a schema with one command? This one doesn't work:

Code: Select all

dbadmin=> select analyze_statistics('mydata_wh');
ERROR 2065:  analyze_statistics: Invalid table/projection/column mydata_wh

[jpcavanaugh]

Just give it an empty string as the argument.

Code: Select all

select analyze_statistics('');

[Julie]

Thanks! But does passing in no argments analyze the tables in all schemas of the active database? We have 10 schemas, but I just want to analyze the 100 or so tables in one of those schemas.

[Julie]

My original post title was misleading so it from "How to analyze all schema tables" to "How to analyze all tables in one schema (edited)".

[JimKnicely]

Hi Julie,

I do something like the following to generate a script file that contains all the analyze commands for the tables in a single schema and the run the script file:

Code: Select all

dbadmin=> \t
Tuples only is off.
dbadmin=> \t
Showing only tuples.
dbadmin=> \o /usr/home/dbadmin/analyze.sql
dbadmin=> select 'select analyze_statistics(''' || table_schema || '.' || table_name || ''');' from tables where table_schema = 'myschema';
dbadmin=> \o
dbadmin=> \i /usr/home/dbadmin/analyze.sql

                  0

                  0

                  0

                  0

                  0

                  0

                  0
 
etc...

Hope this helps!