snipt

Ctrl+h for KB shortcuts

Text only

Check weight of mysql's tables

SELECT  CONCAT(table_schema, '.', table_name),     CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,         CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,         CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,         CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'Gb') total_size,          ROUND(index_length / data_length, 2) idxfrac FROM   information_schema.TABLES ORDER  BY data_length + index_length DESC LIMIT 20;
https://snipt.net/embed/06cbb540c93ca91a41dd62ff03703f20/
https://snipt.net/raw/06cbb540c93ca91a41dd62ff03703f20/
06cbb540c93ca91a41dd62ff03703f20
text
Text only
2
2016-09-23T04:46:13
True
False
False
Jun 12, 2013 at 07:59 AM
/api/public/snipt/63938/
check-weight-of-mysqls-tables
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a>SELECT CONCAT(table_schema, &#39;.&#39;, table_name), CONCAT(ROUND(table_rows / 1000000, 2), &#39;M&#39;) rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), &#39;G&#39;) DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), &#39;G&#39;) idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), &#39;Gb&#39;) total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 20; </span></pre></div> </td></tr></table>
mysql, tables

MySQL

mysql drop all tables in a database

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
https://snipt.net/embed/cce3dccb0e656597a48714c7b7de0076/
https://snipt.net/raw/cce3dccb0e656597a48714c7b7de0076/
cce3dccb0e656597a48714c7b7de0076
mysql
MySQL
1
2016-09-24T04:29:58
True
False
False
/api/public/snipt/23487/
mysql-drop-all-tables-in-a-database
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="n">mysqldump</span> <span class="o">-</span><span class="n">u</span><span class="p">[</span><span class="n">USERNAME</span><span class="p">]</span> <span class="o">-</span><span class="n">p</span><span class="p">[</span><span class="n">PASSWORD</span><span class="p">]</span> <span class="o">--</span><span class="k">add</span><span class="o">-</span><span class="k">drop</span><span class="o">-</span><span class="k">table</span> <span class="o">--</span><span class="n">no</span><span class="o">-</span><span class="n">data</span> <span class="p">[</span><span class="k">DATABASE</span><span class="p">]</span> <span class="o">|</span> <span class="n">grep</span> <span class="o">^</span><span class="k">DROP</span> <span class="o">|</span> <span class="n">mysql</span> <span class="o">-</span><span class="n">u</span><span class="p">[</span><span class="n">USERNAME</span><span class="p">]</span> <span class="o">-</span><span class="n">p</span><span class="p">[</span><span class="n">PASSWORD</span><span class="p">]</span> <span class="p">[</span><span class="k">DATABASE</span><span class="p">]</span> </span></pre></div> </td></tr></table>
drop, mysql, tables

Bash

Optimize all tables on MySQL database

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
https://snipt.net/embed/6fa5b3037a9a53857f6010d06bd11d1c/
https://snipt.net/raw/6fa5b3037a9a53857f6010d06bd11d1c/
6fa5b3037a9a53857f6010d06bd11d1c
bash
Bash
1
2016-09-22T07:24:12
True
False
False
/api/public/snipt/16014/
optimize-all-tables-on-mysql-database
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a>mysqlcheck -u root -p --auto-repair --check --optimize --all-databases </span></pre></div> </td></tr></table>
database, mysql, optimize, repair, tables

SQL

Count number of tables in a SQL Server database

1
2
3
USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table' 
https://snipt.net/embed/340d7c99c9bbb6921fe7594ef15e6a0d/
https://snipt.net/raw/340d7c99c9bbb6921fe7594ef15e6a0d/
340d7c99c9bbb6921fe7594ef15e6a0d
sql
SQL
3
2016-09-25T02:05:34
True
False
False
/api/public/snipt/14897/
count-number-of-tables-in-a-sql-server-database
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="n">USE</span> <span class="n">YOURDBNAME</span> </span><span id="L-2"><a name="L-2"></a><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">from</span> <span class="n">information_schema</span><span class="p">.</span><span class="n">tables</span> </span><span id="L-3"><a name="L-3"></a><span class="k">WHERE</span> <span class="n">table_type</span> <span class="o">=</span> <span class="s1">&#39;base table&#39;</span> </span></pre></div> </td></tr></table>
count, tables, tsql

MySQL

Find 10 largest MySQL Tables

SELECT concat(table_schema,'.',table_name),concat(round(table_rows/1000000,2),'M') rows,concat(round(data_length/(1024*1024*1024),2),'G') DATA,concat(round(index_length/(1024*1024*1024),2),'G') idx,concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 10;
https://snipt.net/embed/fcad8409f3be4cdf745de5ee524d5445/
https://snipt.net/raw/fcad8409f3be4cdf745de5ee524d5445/
fcad8409f3be4cdf745de5ee524d5445
mysql
MySQL
1
2016-09-24T08:31:05
True
False
False
/api/public/snipt/14507/
find-10-largest-mysql-tables
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="nf">concat</span><span class="p">(</span><span class="n">table_schema</span><span class="p">,</span><span class="s1">&#39;.&#39;</span><span class="p">,</span><span class="n">table_name</span><span class="p">),</span><span class="nf">concat</span><span class="p">(</span><span class="nf">round</span><span class="p">(</span><span class="n">table_rows</span><span class="o">/</span><span class="mi">1000000</span><span class="p">,</span><span class="mi">2</span><span class="p">),</span><span class="s1">&#39;M&#39;</span><span class="p">)</span> <span class="n">rows</span><span class="p">,</span><span class="nf">concat</span><span class="p">(</span><span class="nf">round</span><span class="p">(</span><span class="n">data_length</span><span class="o">/</span><span class="p">(</span><span class="mi">1024</span><span class="o">*</span><span class="mi">1024</span><span class="o">*</span><span class="mi">1024</span><span class="p">),</span><span class="mi">2</span><span class="p">),</span><span class="s1">&#39;G&#39;</span><span class="p">)</span> <span class="n">DATA</span><span class="p">,</span><span class="nf">concat</span><span class="p">(</span><span class="nf">round</span><span class="p">(</span><span class="n">index_length</span><span class="o">/</span><span class="p">(</span><span class="mi">1024</span><span class="o">*</span><span class="mi">1024</span><span class="o">*</span><span class="mi">1024</span><span class="p">),</span><span class="mi">2</span><span class="p">),</span><span class="s1">&#39;G&#39;</span><span class="p">)</span> <span class="n">idx</span><span class="p">,</span><span class="nf">concat</span><span class="p">(</span><span class="nf">round</span><span class="p">((</span><span class="n">data_length</span><span class="o">+</span><span class="n">index_length</span><span class="p">)</span><span class="o">/</span><span class="p">(</span><span class="mi">1024</span><span class="o">*</span><span class="mi">1024</span><span class="o">*</span><span class="mi">1024</span><span class="p">),</span><span class="mi">2</span><span class="p">),</span><span class="s1">&#39;G&#39;</span><span class="p">)</span> <span class="n">total_size</span><span class="p">,</span><span class="nf">round</span><span class="p">(</span><span class="n">index_length</span><span class="o">/</span><span class="n">data_length</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span> <span class="n">idxfrac</span> <span class="k">FROM</span> <span class="n">information_schema</span><span class="p">.</span><span class="kp">TABLES</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">data_length</span><span class="o">+</span><span class="n">index_length</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="mi">10</span><span class="p">;</span> </span></pre></div> </td></tr></table>
mysql, query, size, space, tables
Copyrighted, illegal, or inappropriate content? Email support@snipt.net.