{"id":122,"date":"2023-07-20T15:10:41","date_gmt":"2023-07-20T13:10:41","guid":{"rendered":"http:\/\/130.61.57.200\/?p=122"},"modified":"2023-07-30T02:21:40","modified_gmt":"2023-07-30T00:21:40","slug":"find-all-rows-of-tables-in-current-schema","status":"publish","type":"post","link":"http:\/\/130.61.57.200\/index.php\/2023\/07\/20\/find-all-rows-of-tables-in-current-schema\/","title":{"rendered":"Find all rows of tables in current schema"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>WITH tbl AS\n  (SELECT table_schema,\n          TABLE_NAME\n   FROM information_schema.tables\n   WHERE TABLE_NAME not like &#039;pg_%&#039;\n     AND table_schema not in (&#039;information_schema&#039;))\nSELECT table_schema,\n       TABLE_NAME,\n       (xpath(&#039;\/row\/c\/text()&#039;, query_to_xml(format(&#039;select count(*) as c from %I.%I&#039;, table_schema, TABLE_NAME), FALSE, TRUE, &#039;&#039;)))&#091;1]::text::int AS rows_n\nFROM tbl\nORDER BY rows_n DESC;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":130,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[43,38],"class_list":["post-122","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-rows","tag-table"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/122","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=122"}],"version-history":[{"count":2,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/122\/revisions"}],"predecessor-version":[{"id":124,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/122\/revisions\/124"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/130"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=122"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=122"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=122"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}