Snipt.net is closing on December 31st, 2016

For more information, please visit this blog post.

snipt

Ctrl+h for KB shortcuts

SQL

Generate Statements for MSSMS Export

1
2
3
4
5
6
SELECT 'EXEC sp_generate_inserts ' + 
'[' + name + ']' + 
', @ommit_images = 1, @disable_constraints = 1'
FROM sysobjects 
WHERE type = 'U' AND 
OBJECTPROPERTY(id,'ismsshipped') = 0
https://snipt.net/embed/ae2fee6f58c2c8f57da8edb197d4a572/
https://snipt.net/raw/ae2fee6f58c2c8f57da8edb197d4a572/
ae2fee6f58c2c8f57da8edb197d4a572
sql
SQL
6
2016-12-06T03:06:38
True
False
False
/api/public/snipt/98492/
generate-statements-for-mssms-export
<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> <a href="#L-5">5</a> <a href="#L-6">6</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="s1">&#39;EXEC sp_generate_inserts &#39;</span> <span class="o">+</span> </span><span id="L-2"><a name="L-2"></a><span class="s1">&#39;[&#39;</span> <span class="o">+</span> <span class="n">name</span> <span class="o">+</span> <span class="s1">&#39;]&#39;</span> <span class="o">+</span> </span><span id="L-3"><a name="L-3"></a><span class="s1">&#39;, @ommit_images = 1, @disable_constraints = 1&#39;</span> </span><span id="L-4"><a name="L-4"></a><span class="k">FROM</span> <span class="n">sysobjects</span> </span><span id="L-5"><a name="L-5"></a><span class="k">WHERE</span> <span class="k">type</span> <span class="o">=</span> <span class="s1">&#39;U&#39;</span> <span class="k">AND</span> </span><span id="L-6"><a name="L-6"></a><span class="n">OBJECTPROPERTY</span><span class="p">(</span><span class="n">id</span><span class="p">,</span><span class="s1">&#39;ismsshipped&#39;</span><span class="p">)</span> <span class="o">=</span> <span class="mi">0</span> </span></pre></div> </td></tr></table>
"microsoft sql", "stored procedure", mssms, mssql, procedure, sql

SQL

Add a column in a MSSQL table

1
2
3
ALTER TABLE [DBName].[dbo].[TableName]
ADD ColumnName int NOT NULL DEFAULT(DefaultValuefortheColumn)
GO
https://snipt.net/embed/7778fc4ef5c1db45b3c8c428d879c163/
https://snipt.net/raw/7778fc4ef5c1db45b3c8c428d879c163/
7778fc4ef5c1db45b3c8c428d879c163
sql
SQL
3
2016-12-10T15:11:11
True
False
False
/api/public/snipt/43198/
add-a-column-in-a-mssql-table
<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></span><span id="L-1"><a name="L-1"></a><span class="k">ALTER</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">DBName</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">TableName</span><span class="p">]</span> </span><span id="L-2"><a name="L-2"></a><span class="k">ADD</span> <span class="n">ColumnName</span> <span class="nb">int</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">DEFAULT</span><span class="p">(</span><span class="n">DefaultValuefortheColumn</span><span class="p">)</span> </span><span id="L-3"><a name="L-3"></a><span class="k">GO</span> </span></pre></div> </td></tr></table>
mssql

SQL

Rename a column in a MSSQL table

EXEC sp_rename 'dbo.tableName.OldColumnName', 'NewColumnName', 'COLUMN'
https://snipt.net/embed/0250de6067f437f5e11a9cd7ee610426/
https://snipt.net/raw/0250de6067f437f5e11a9cd7ee610426/
0250de6067f437f5e11a9cd7ee610426
sql
SQL
1
2016-12-01T03:50:33
True
False
False
/api/public/snipt/43197/
rename-a-column-in-a-mssql-table
<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><span class="k">EXEC</span> <span class="n">sp_rename</span> <span class="s1">&#39;dbo.tableName.OldColumnName&#39;</span><span class="p">,</span> <span class="s1">&#39;NewColumnName&#39;</span><span class="p">,</span> <span class="s1">&#39;COLUMN&#39;</span> </span></pre></div> </td></tr></table>
mssql

SQL

Count all rows for each table

1
2
3
4
5
6
--Count all rows for each table
select   o.name,            i.rowcnt 
from      sysobjects as o join sysindexes as i 
on        o.id = i.id 
where   o.xtype = 'U' and            i.indid in (0, 1) 
order     by i.rowcnt desc, o.name 
https://snipt.net/embed/f4e52887763c5da6d8371700ec4be5b6/
https://snipt.net/raw/f4e52887763c5da6d8371700ec4be5b6/
f4e52887763c5da6d8371700ec4be5b6
sql
SQL
7
2016-12-10T08:05:04
True
False
False
/api/public/snipt/34416/
count-all-rows-for-each-table
<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> <a href="#L-5">5</a> <a href="#L-6">6</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">--Count all rows for each table</span> </span><span id="L-2"><a name="L-2"></a><span class="k">select</span> <span class="n">o</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">i</span><span class="p">.</span><span class="n">rowcnt</span> </span><span id="L-3"><a name="L-3"></a><span class="k">from</span> <span class="n">sysobjects</span> <span class="k">as</span> <span class="n">o</span> <span class="k">join</span> <span class="n">sysindexes</span> <span class="k">as</span> <span class="n">i</span> </span><span id="L-4"><a name="L-4"></a><span class="k">on</span> <span class="n">o</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">i</span><span class="p">.</span><span class="n">id</span> </span><span id="L-5"><a name="L-5"></a><span class="k">where</span> <span class="n">o</span><span class="p">.</span><span class="n">xtype</span> <span class="o">=</span> <span class="s1">&#39;U&#39;</span> <span class="k">and</span> <span class="n">i</span><span class="p">.</span><span class="n">indid</span> <span class="k">in</span> <span class="p">(</span><span class="mi">0</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span> </span><span id="L-6"><a name="L-6"></a><span class="k">order</span> <span class="k">by</span> <span class="n">i</span><span class="p">.</span><span class="n">rowcnt</span> <span class="k">desc</span><span class="p">,</span> <span class="n">o</span><span class="p">.</span><span class="n">name</span> </span></pre></div> </td></tr></table>
mssql

SQL

Clear buffer Sql Server

1
2
3
--Clear Buffer SQL SERVER
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
https://snipt.net/embed/51841831d26c12030f53635bfbe9a887/
https://snipt.net/raw/51841831d26c12030f53635bfbe9a887/
51841831d26c12030f53635bfbe9a887
sql
SQL
3
2016-12-09T11:23:26
True
False
False
/api/public/snipt/34369/
clear-buffer-sql-server
<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></span><span id="L-1"><a name="L-1"></a><span class="c1">--Clear Buffer SQL SERVER</span> </span><span id="L-2"><a name="L-2"></a><span class="n">DBCC</span> <span class="n">DROPCLEANBUFFERS</span> </span><span id="L-3"><a name="L-3"></a><span class="n">DBCC</span> <span class="n">FREEPROCCACHE</span> </span></pre></div> </td></tr></table>
buffer, mssql, sql

SQL

Search Stored Procedures

1
2
3
4
5
SELECT	ROUTINE_NAME
FROM	INFORMATION_SCHEMA.ROUTINES 
WHERE	ROUTINE_DEFINITION LIKE '%%' 
		AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME
https://snipt.net/embed/26e17738fa5eb78c895cd81f785a383b/
https://snipt.net/raw/26e17738fa5eb78c895cd81f785a383b/
26e17738fa5eb78c895cd81f785a383b
sql
SQL
5
2016-12-10T10:48:28
True
False
False
/api/public/snipt/32138/
search-stored-procedures
<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> <a href="#L-5">5</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="k">ROUTINE_NAME</span> </span><span id="L-2"><a name="L-2"></a><span class="k">FROM</span> <span class="n">INFORMATION_SCHEMA</span><span class="p">.</span><span class="n">ROUTINES</span> </span><span id="L-3"><a name="L-3"></a><span class="k">WHERE</span> <span class="n">ROUTINE_DEFINITION</span> <span class="k">LIKE</span> <span class="s1">&#39;%%&#39;</span> </span><span id="L-4"><a name="L-4"></a> <span class="k">AND</span> <span class="n">ROUTINE_TYPE</span> <span class="o">=</span> <span class="s1">&#39;PROCEDURE&#39;</span> </span><span id="L-5"><a name="L-5"></a><span class="k">ORDER</span> <span class="k">BY</span> <span class="k">ROUTINE_NAME</span> </span></pre></div> </td></tr></table>
mssql

SQL

Generate row-level random number on MSSQL

SELECT ABS(CHECKSUM(NewId()) % 15) 
https://snipt.net/embed/c9f34f83af0fba6a4fb1e40b3ad38248/
https://snipt.net/raw/c9f34f83af0fba6a4fb1e40b3ad38248/
c9f34f83af0fba6a4fb1e40b3ad38248
sql
SQL
1
2016-12-06T04:42:14
True
False
False
/api/public/snipt/28436/
generate-row-level-random-number-on-mssql
<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><span class="k">SELECT</span> <span class="k">ABS</span><span class="p">(</span><span class="n">CHECKSUM</span><span class="p">(</span><span class="n">NewId</span><span class="p">())</span> <span class="o">%</span> <span class="mi">15</span><span class="p">)</span> </span></pre></div> </td></tr></table>
mssql, random
Copyrighted, illegal, or inappropriate content? Email [email protected].