Hidden Features of SQL Server

What are some hidden features of [SQL Server][1]?

For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough?


----------

## Answers ##

*Thanks to everybody for all the great answers!*

**Stored Procedures**

- **sp_msforeachtable:** Runs a command with '?' replaced with each table name (v6.5 and up)
- **sp_msforeachdb:** Runs a command with '?' replaced with each database name (v7 and up)
- **sp\_who2:** just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
- **sp_helptext:** If you want the code of a stored procedure, view & UDF
- **sp_tables:** return a list of all tables and views of database in scope.
- **sp_stored_procedures:** return a list of all stored procedures
- **xp_sscanf:** Reads data from the string into the argument locations specified by each format argument.
- **xp_fixeddrives:**: Find the fixed drive with largest free space
- **sp_help:** If you want to know the table structure, indexes and constraints of a table. Also views and UDFs. Shortcut is Alt+F1

**Snippets**

- Returning rows in random order
- All database User Objects by Last Modified Date
- Return Date Only
- Find records which date falls somewhere inside the current week.
- Find records which date occurred last week.
- Returns the date for the beginning of the current week.
- Returns the date for the beginning of last week.
- See the text of a procedure that has been deployed to a server
- Drop all connections to the database
- Table Checksum
- Row Checksum
- Drop all the procedures in a database
- Re-map the login Ids correctly after restore
- Call Stored Procedures from an INSERT statement
- Find Procedures By Keyword
- Drop all the procedures in a database
- Query the transaction log for a database programmatically.

**Functions**

- HashBytes()
- EncryptByKey
- PIVOT command

**Misc**

- Connection String extras
- TableDiff.exe
- Triggers for Logon Events (New in Service Pack 2)
- Boosting performance with persisted-computed-columns (pcc).
- DEFAULT_SCHEMA setting in sys.database_principles
- Forced Parameterization
- Vardecimal Storage Format
- Figuring out the most popular queries in seconds
- Scalable Shared Databases
- Table/Stored Procedure Filter feature in SQL Management Studio
- Trace flags
- Number after a `GO` repeats the batch
- Security using schemas
- Encryption using built in encryption functions, views and base tables with triggers


[1]:

[To see links please register here]

Here are some features I find useful but a lot of people don't seem to know about:

sp_tables

> Returns a list of objects that can be
> queried in the current environment.
> This means any object that can appear
> in a FROM clause, except synonym
> objects.

[Link][1]

sp_stored_procedures

> Returns a list of stored procedures in
> the current environment.

[Link][2]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

Simple encryption with [EncryptByKey][1]


[1]:

[To see links please register here]

sp_who2, just like sp_who, but with a lot more info for troubleshooting blocks

/* Find the fixed drive with largest free space, you can also copy files to estimate which disk is quickest */

EXEC master..xp_fixeddrives

/* Checking assumptions about a file before use or reference */

EXEC master..xp_fileexist 'C:\file_you_want_to_check'

[More details here][1]


[1]:

[To see links please register here]

Connection String extras:

**MultipleActiveResultSets=true;**

This makes ADO.Net 2.0 and above read multiple, forward-only, read-only results sets on a single database connection, which can improve performance if you're doing a lot of reading. You can turn it on even if you're doing a mix of query types.

**Application Name=MyProgramName**

Now when you want to see a list of active connections by querying the sysprocesses table, your program's name will appear in the program_name column instead of ".Net SqlClient Data Provider"

A semi-hidden feature, the Table/Stored Procedure Filter feature can be really useful...

In the **SQL Server Management Studio** *Object Explorer*, right-click the **Tables** or **Stored Procedures** folder, select the **Filter** menu, then **Filter Settings**, and enter a partial name in the *Name contains* row.

Likewise, use **Remove Filter** to see all Tables/Stored Procedures again.

Find records which date falls somewhere inside the current week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ), 0 )

Find records which date occurred last week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Returns the date for the beginning of the current week.

select dateadd( week, datediff( week, 0, getdate() ), 0 )

Returns the date for the beginning of last week.

select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Drop all connections to the database:

Use Master
Go

Declare @dbname sysname

Set @dbname = 'name of database you want to drop connections from'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End

Table Checksum

Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)

Row Checksum

Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK) Where Column = Value