title: Hidden Features of SQL Server
body: <p>What are some hidden features of <a href="http://en.wikipedia.org/wiki/Microsoft_SQL_Server" rel="nofollow noreferrer">SQL Server</a>?</p>
  
  <p>For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough?</p>
  
  <hr>
  
  <h2>Answers</h2>
  
  <p><em>Thanks to everybody for all the great answers!</em></p>
  
  <p><strong>Stored Procedures</strong></p>
  
  <ul>
  <li><strong>sp_msforeachtable:</strong> Runs a command with '?' replaced with each table name (v6.5 and up)</li>
  <li><strong>sp_msforeachdb:</strong> Runs a command with '?' replaced with each database name (v7 and up)</li>
  <li><strong>sp_who2:</strong> just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)</li>
  <li><strong>sp_helptext:</strong> If you want the code of a stored procedure, view &amp; UDF</li>
  <li><strong>sp_tables:</strong> return a list of all tables and views of database in scope.</li>
  <li><strong>sp_stored_procedures:</strong> return a list of all stored procedures</li>
  <li><strong>xp_sscanf:</strong> Reads data from the string into the argument locations specified by each format argument.</li>
  <li><strong>xp_fixeddrives:</strong>: Find the fixed drive with largest free space</li>
  <li><strong>sp_help:</strong> If you want to know the table structure, indexes and constraints of a table. Also views and UDFs. Shortcut is Alt+F1</li>
  </ul>
  
  <p><strong>Snippets</strong></p>
  
  <ul>
  <li>Returning rows in random order</li>
  <li>All database User Objects by Last Modified Date</li>
  <li>Return Date Only</li>
  <li>Find records which date falls somewhere inside the current week.</li>
  <li>Find records which date occurred last week.</li>
  <li>Returns the date for the beginning of the current week.</li>
  <li>Returns the date for the beginning of last week.</li>
  <li>See the text of a procedure that has been deployed to a server</li>
  <li>Drop all connections to the database</li>
  <li>Table Checksum</li>
  <li>Row Checksum</li>
  <li>Drop all the procedures in a database</li>
  <li>Re-map the login Ids correctly after restore</li>
  <li>Call Stored Procedures from an INSERT statement</li>
  <li>Find Procedures By Keyword</li>
  <li>Drop all the procedures in a database</li>
  <li>Query the transaction log for a database programmatically.</li>
  </ul>
  
  <p><strong>Functions</strong></p>
  
  <ul>
  <li>HashBytes()</li>
  <li>EncryptByKey</li>
  <li>PIVOT command</li>
  </ul>
  
  <p><strong>Misc</strong></p>
  
  <ul>
  <li>Connection String extras</li>
  <li>TableDiff.exe</li>
  <li>Triggers for Logon Events (New in Service Pack 2)</li>
  <li>Boosting performance with persisted-computed-columns (pcc).</li>
  <li>DEFAULT_SCHEMA setting in sys.database_principles</li>
  <li>Forced Parameterization</li>
  <li>Vardecimal Storage Format</li>
  <li>Figuring out the most popular queries in seconds</li>
  <li>Scalable Shared Databases</li>
  <li>Table/Stored Procedure Filter feature in SQL Management Studio</li>
  <li>Trace flags</li>
  <li>Number after a <code>GO</code> repeats the batch</li>
  <li>Security using schemas</li>
  <li>Encryption using built in encryption functions, views and base tables with triggers</li>
  </ul>