Database thingies

Evo jedan SQL upit, pomocu kojeg mozete saznati neke informacije o zauzecu vasih baza, broju tabela u bazi i sl.

SELECT s.schema_name naziv_baze,
  CONCAT(
    IFNULL(
      ROUND(
       (SUM(t.data_length)+SUM(t.index_length))/1024/1024
      ,3)
     ,0.00)
   ,' MB') as ukupna_velicina_baze,
  CONCAT(
    IFNULL(
      ROUND(
        (
          (SUM(t.data_length)+SUM(t.index_length))
          - SUM(t.data_free)
        )/1024/1024
      , 3)
    , 0.00)
   ,' MB') as iskoristeno_MB,
  CONCAT(
    IFNULL(
      ROUND(SUM(data_free)/1024/1024,3)
    ,0.00)
  ,' MB') as slobodno_MB,
  CONCAT(
    IFNULL(
      ROUND(
       (
         (
          (SUM(t.data_length)+SUM(t.index_length))
           -SUM(t.data_free)
         ) /
         ((SUM(t.data_length)+SUM(t.index_length)))*100)
      ,3)
    ,0)
   ,' %') as iskoristeno_procenata,
  COUNT(table_name) as broj_tabela
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t
       ON s.schema_name = t.table_schema
WHERE s.schema_name != 'information_schema'
GROUP BY s.schema_name
ORDER BY 1;

This entry was posted on Friday, November 3rd, 2006 at 1:29 am and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.