snipt

Ctrl+h for KB shortcuts

SQL

Enable/Disable ALL Constraint

1
2
3
4
--disable All constraint keys
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
--enable All constraint keys
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
https://snipt.net/embed/a4fc9de69d5fe14ddc9a4fb78003ea28/
/raw/a4fc9de69d5fe14ddc9a4fb78003ea28/
a4fc9de69d5fe14ddc9a4fb78003ea28
sql
SQL
4
2019-05-17T16:11:11
True
False
False
/api/public/snipt/33917/
enabledisable-all-constraint
<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> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">--disable All constraint keys</span> </span><span id="L-2"><a name="L-2"></a><span class="k">EXEC</span> <span class="n">sp_msforeachtable</span> <span class="ss">&quot;ALTER TABLE ? NOCHECK CONSTRAINT all&quot;</span> </span><span id="L-3"><a name="L-3"></a><span class="c1">--enable All constraint keys</span> </span><span id="L-4"><a name="L-4"></a><span class="k">exec</span> <span class="n">sp_msforeachtable</span> <span class="o">@</span><span class="n">command1</span><span class="o">=</span><span class="ss">&quot;print &#39;?&#39;&quot;</span><span class="p">,</span> <span class="o">@</span><span class="n">command2</span><span class="o">=</span><span class="ss">&quot;ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all&quot;</span> </span></pre></div> </td></tr></table>
dba, sql

Bash

mysql: check all tables in all databases

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
https://snipt.net/embed/6d4279ae5b219885f32e6eee51071999/
/raw/6d4279ae5b219885f32e6eee51071999/
6d4279ae5b219885f32e6eee51071999
bash
Bash
1
2019-05-24T14:05:14
True
False
False
/api/public/snipt/2043/
mysql-check-all-tables-in-all-databases
<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></span><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>
dba, mysql, sysadmin