Hidden Features of SQL Server

What are some hidden features of SQL Server?

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

In Management Studio, you can put a number after a GO end-of-batch marker to cause the batch to be repeated that number of times:

PRINT 'X'
GO 10

Will print 'X' 10 times. This can save you from tedious copy/pasting when doing repetitive stuff.

A lot of SQL Server developers still don't seem to know about the OUTPUT clause (SQL Server 2005 and newer) on the DELETE, INSERT and UPDATE statement.

It can be extremely useful to know which rows have been INSERTed, UPDATEd, or DELETEd, and the OUTPUT clause allows to do this very easily - it allows access to the "virtual" tables called inserted and deleted (like in triggers):

DELETE FROM (table)
OUTPUT deleted.ID, deleted.Description
WHERE (condition)

If you're inserting values into a table which has an INT IDENTITY primary key field, with the OUTPUT clause, you can get the inserted new ID right away:

INSERT INTO MyTable(Field1, Field2)
OUTPUT inserted.ID
VALUES (Value1, Value2)

And if you're updating, it can be extremely useful to know what changed - in this case, inserted represents the new values (after the UPDATE), while deleted refers to the old values before the UPDATE:

UPDATE (table)
SET field1 = value1, field2 = value2
OUTPUT inserted.ID, deleted.field1, inserted.field1
WHERE (condition)

If a lot of info will be returned, the output of OUTPUT can also be redirected to a temporary table or a table variable (OUTPUT INTO @myInfoTable).

Extremely useful - and very little known!

Marc

sp_msforeachtable: Runs a command with '?' replaced with each table name. e.g.

exec sp_msforeachtable "dbcc dbreindex('?')"

You can issue up to 3 commands for each table

exec sp_msforeachtable
	@Command1 = 'print ''reindexing table ?''',
	@Command2 = 'dbcc dbreindex(''?'')',
	@Command3 = 'select count (*) [?] from ?'

Also, sp_MSforeachdb

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"

TableDiff.exe

• Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables.

Link

A less known TSQL technique for returning rows in random order:

-- Return rows in a random order
SELECT 
	SomeColumn 
FROM 
	SomeTable
ORDER BY 
	CHECKSUM(NEWID())

In Management Studio, you can quickly get a comma-delimited list of columns for a table by :

1. In the Object Explorer, expand the nodes under a given table (so you will see folders for Columns, Keys, Constraints, Triggers etc.)
2. Point to the Columns folder and drag into a query.

This is handy when you don't want to use heinous format returned by right-clicking on the table and choosing Script Table As..., then Insert To... This trick does work with the other folders in that it will give you a comma-delimited list of names contained within the folder.

Row Constructors

You can insert multiple rows of data with a single insert statement.

INSERT INTO Colors (id, Color)
VALUES (1, 'Red'),
       (2, 'Blue'),
       (3, 'Green'),
       (4, 'Yellow')

HashBytes() to return the MD2, MD4, MD5, SHA, or SHA1 hash of its input.

If you want to know the table structure, indexes and constraints:

sp_help 'TableName'

Figuring out the most popular queries

• With sys.dm_exec_query_stats, you can figure out many combinations of query analyses by a single query.

[Link][1] [1]: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/gems-top-10.mspx

with the commnad

select * from sys.dm_exec_query_stats 
order by execution_count desc

The spatial results tab can be used to create art.

useful when restoring a database for Testing purposes or whatever. Re-maps the login ID's correctly:

EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'

I know it's not exactly hidden, but not too many people know about the PIVOT command. I was able to change a stored procedure that used cursors and took 2 minutes to run into a speedy 6 second piece of code that was one tenth the number of lines!

EXCEPT and INTERSECT

Instead of writing elaborate joins and subqueries, these two keywords are a much more elegant shorthand and readable way of expressing your query's intent when comparing two query results. New as of SQL Server 2005, they strongly complement UNION which has already existed in the TSQL language for years.

The concepts of EXCEPT, INTERSECT, and UNION are fundamental in set theory which serves as the basis and foundation of relational modeling used by all modern RDBMS. Now, Venn diagram type results can be more intuitively and quite easily generated using TSQL.

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

I'm not sure if this is a hidden feature or not, but I stumbled upon this, and have found it to be useful on many occassions. You can concatonate a set of a field in a single select statement, rather than using a cursor and looping through the select statement.

Example:

DECLARE @nvcConcatonated nvarchar(max)
SET @nvcConcatonated = ''

SELECT @nvcConcatonated = @nvcConcatonated + C.CompanyName + ', '
FROM tblCompany C
WHERE C.CompanyID IN (1,2,3)

SELECT @nvcConcatonated

Results:

Acme, Microsoft, Apple,

If you want the code of a stored procedure you can:

sp_helptext 'ProcedureName'

(not sure if it is hidden feature, but I use it all the time)

A stored procedure trick is that you can call them from an INSERT statement. I found this very useful when I was working on an SQL Server database.

CREATE TABLE #toto (v1 int, v2 int, v3 char(4), status char(6))
INSERT #toto (v1, v2, v3, status) EXEC dbo.sp_fulubulu(sp_param1)
SELECT * FROM #toto
DROP TABLE #toto

In SQL Server 2005/2008 to show row numbers in a SELECT query result:

SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber,
        GrandTotal, CustomerId, PurchaseDate
FROM Orders

ORDER BY is a compulsory clause. The OVER() clause tells the SQL Engine to sort data on the specified column (in this case OrderId) and assign numbers as per the sort results.

Useful for parsing stored procedure arguments: xp_sscanf

> Reads data from the string into the argument locations specified by each format argument. > > The following example uses xp_sscanf > to extract two values from a source > string based on their positions in the > format of the source string.

DECLARE @filename varchar (20), @message varchar (20)
EXEC xp_sscanf 'sync -b -fproducts10.tmp -rrandom', 'sync -b -f%s -r%s', 
  @filename OUTPUT, @message OUTPUT
SELECT @filename, @message

> Here is the result set.

-------------------- -------------------- 
products10.tmp        random

Return Date Only

Select Cast(Floor(Cast(Getdate() As Float))As Datetime)

or

Select DateAdd(Day, 0, DateDiff(Day, 0, Getdate()))

dm_db_index_usage_stats

This allows you to know if data in a table has been updated recently even if you don't have a DateUpdated column on the table.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'MyDatabase')
AND OBJECT_ID=OBJECT_ID('MyTable')

Code from: http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/

Information referenced from: https://stackoverflow.com/questions/837709/sql-server-what-is-the-date-time-of-the-last-inserted-row-of-a-table

Available in SQL 2005 and later

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

sp_stored_procedures

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

Link

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 )

Not so much a hidden feature but setting up key mappings in Management Studio under Tools\Options\Keyboard: Alt+F1 is defaulted to sp_help "selected text" but I cannot live without the adding Ctrl+F1 for sp_helptext "selected text"

Persisted-computed-columns

• Computed columns can help you shift the runtime computation cost to data modification phase. The computed column is stored with the rest of the row and is transparently utilized when the expression on the computed columns and the query matches. You can also build indexes on the PCC’s to speed up filtrations and range scans on the expression.

Link

There are times when there's no suitable column to sort by, or you just want the default sort order on a table and you want to enumerate each row. In order to do that you can put "(select 1)" in the "order by" clause and you'd get what you want. Neat, eh?

select row_number() over (order by (select 1)), * from dbo.Table as t

Simple encryption with EncryptByKey

/* 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

The most surprising thing I learned this week involved using a CASE statement in the ORDER By Clause. For example:

declare @orderby varchar(10)

set @orderby = 'NAME'

select * 
	from Users
	ORDER BY 
		CASE @orderby
			WHEN 'NAME' THEN LastName
			WHEN 'EMAIL' THEN EmailAddress
		END
			
			

SQLCMD

If you've got scripts that you run over and over, but have to change slight details, running ssms in sqlcmd mode is awesome. The sqlcmd command line is pretty spiffy too.

My favourite features are:

• You get to set variables. Proper variables that don't require jumping through sp_exec hoops
• You can run multiple scripts one after the other
• Those scripts can reference the variables in the "outer" script

Rather than gushing any more, Simpletalk by Red Gate did an awesome wrap up of sqlcmd - The SQLCMD Workbench. Donabel Santos has some great SQLCMD examples too.

Here's a simple but useful one:

When you're editing table contents manually, you can insert NULL in a column by typing Control-0.

Here is a query I wrote to list All DB User Objects by Last Modified Date:

select name, modify_date, 
case when type_desc = 'USER_TABLE' then 'Table'
when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION') then 'Function'
end as type_desc
from sys.objects
where type in ('U', 'P', 'FN', 'IF', 'TF')
and is_ms_shipped = 0
order by 2 desc

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

I find this small script very handy to see the text of a procedure that has been deployed to a server:

DECLARE @procedureName NVARCHAR( MAX ), @procedureText NVARCHAR( MAX )

SET @procedureName = 'myproc_Proc1'

SET @procedureText =    (
                            SELECT  OBJECT_DEFINITION( object_id )
                            FROM    sys.procedures 
                            WHERE   Name = @procedureName
                        )

PRINT @procedureText

Trace Flags! "1204" was invaluable in deadlock debugging on SQL Server 2000 (2005 has better tools for this).

Find Procedures By Keyword

What procedures contain a certain piece of text (Table name, column name, variable name, TODO, etc)?

SELECT OBJECT_NAME(ID) FROM SysComments 
WHERE Text LIKE '%SearchString%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1

sp_executesql 

For executing a statement in a string. As good as Execute but can return parameters out

Ok here's the few I've got left, shame I missed the start, but keep it up there's some top stuff here!

Query Analyzer

• Alt+F1 executes sp_help on the selected text
• Alt-D - focus to the database dropdown so you can use select db with cursor keys of letter.

T-Sql

• if (object_id("nameofobject") IS NOT NULL) begin <do something> end - easiest existence check
• sp_locks - more in depth locking informaiton than sp_who2 (which is the first port of call)
• dbcc inputbuffer(spid) - list of top line of executing process (kinda useful but v. brief)
• dbcc outputbuffer(spid) - list of top line of output of executing process

General T-sql tip

• With large volumes use sub queries liberally to process data in sets

> e.g. to obtain a list of married > people over fifty you could select a > set of people who are married in a > subquery and join with a set of the > same people over 50 and output the > joined results - please excuse the > contrived example

Batch Seperator

Most people don't know it, but "GO" is not a SQL command. It is the default batch separator used by the client tools. You can find more info about it in Books Online.

You can change the Batch separator by selecting Tools -> Options in Management Studio, and changing the Batch separator Option in the Query Execution section.

I'm not sure why you would want to do this other than as a prank, but it is a somewhat interesting piece of trivia.

use GETDATE() with + or - to calculate a nearby date

SELECT GETDATE() - 1 -- yesterday, 1 day ago, 24 hours ago
SELECT GETDATE() - .5 -- 12 hours ago
SELECT GETDATE() - .25 -- 6 hours ago
SELECT GETDATE() - (1 / 24.0) -- 1 hour ago (implicit decimal result after division)

In SQL Server Management Studio (SSMS) you can highlight an object name in the Object Explorer and press Ctrl-C to copy the name to the clipboard.

There is no need to press F2 or right-click, rename the object to copy the name.

You can also drag and drop an object from the Object Explorer into your query window.

My favorite is master..xp_cmdshell. It allows you to run commands from a command prompt on the server and see the output. It's extremely useful if you can't login to the server, but you need to get information or control it somehow.

For example, to list the folders on the C: drive of the server where SQL Server is running.

• master..xp_cmdshell 'dir c:'

You can start and stop services, too.

• master..xp_cmdshell 'sc query "My Service"'

• master..xp_cmdshell 'sc stop "My Service"'

• master..xp_cmdshell 'sc start "My Service"'

It's very powerful, but a security risk, also. Many people disable it because it could easily be used do bad things on the server. But, if you have access to it, it can be extremely useful.

Triggers for Logon Events

• Logon triggers can help complement auditing and compliance. For example, logon events can be used for enforcing rules on connections (for example limiting connection through a specific username or limiting connections through a username to a specific time periods) or simply for tracking and recording general connection activity. Just like in any trigger, ROLLBACK cancels the operation that is in execution. In the case of logon event that means canceling the connection establishment. Logon events do not fire when the server is started in the minimal configuration mode or when a connection is established through dedicated admin connection (DAC).

Link

Here is one I learned today because I needed to search for a transaction.

::fn_dblog
This allows you to query the transaction log for a database.

USE mydatabase;
SELECT *
FROM ::fn_dblog(NULL, NULL)

http://killspid.blogspot.com/2006/07/using-fndblog.html

Since I'm a programmer, not a DBA, my favorite hidden feature is the SMO library. You can automate pretty much anything in SQL Server, from database/table/column creation and deletion to scripting to backup and restore. If you can do it in SQL Server Management Studio, you can automate it in SMO.

Based on what appears to be a vehement reaction to it by hardened database developers, the CLR integration would rank right up there. =)

Sql 2000+ DBCC DROPCLEANBUFFERS : Clears the buffers. Useful for testing the speed of queries when the buffer is clean.

Stored proc sp_MSdependencies tells you about object dependencies in a more useful fashion than sp_depends. For some production releases it's convenient to temporarily disable child table constraints, apply changes then reenable the child table constraints. This is a great way of finding objects that depend on your parent table.

This code disables child table constraints:

create table #deps
( oType int,
  oObjName sysname,
  oOwner nvarchar(200),
  oSequence int
)

insert into #deps  
exec sp_MSdependencies @tableName, null, 1315327

exec sp_MSforeachtable @command1 = 'ALTER TABLE ? NOCHECK CONSTRAINT ALL',
@whereand = ' and o.name in (select oObjName from #deps where oType = 8)'

After the change is applied one can run this code to reenable the constraints:

exec sp_MSforeachtable @command1 = 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL',
@whereand = ' and o.name in (select oObjName from #deps where oType = 8)'

The third parameter is called @flags and it controls what sort of dependencies will be listed. Go read the proc contents to see how you can change @flags for your purposes. The proc uses bit masks to decipher what you want returned.

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.

If you want to drop all the procedures in a DB -

SELECT  IDENTITY ( int, 1, 1 ) id, 
        [name] 
INTO    #tmp 
FROM    sys.procedures 
WHERE   [type]        = 'P' 
    AND is_ms_shipped = 0 

DECLARE @i INT 

SELECT   @i = COUNT( id ) FROM #tmp 
WHILE    @i > 0 
BEGIN 
   DECLARE @name VARCHAR( 100 ) 
   SELECT @name = name FROM #tmp WHERE id = @i 
   EXEC ( 'DROP PROCEDURE ' + @name ) 
   SET @i = @i-1 
END

DROP TABLE #tmp

DEFAULT_SCHEMA setting in sys.database_principles

• SQL Server provides great flexibility with name resolution. However name resolution comes at a cost and can get noticeably expensive in adhoc workloads that do not fully qualify object references. SQL Server 2005 allows a new setting of DEFEAULT_SCHEMA for each database principle (also known as “user”) which can eliminate this overhead without changing your TSQL code.

Link

Vardecimal Storage Format

• SQL Server 2005 adds a new storage format for numeric and decimal datatypes called vardecimal. Vardecimal is a variable-length representation for decimal types that can save unused bytes in every instance of the row. The biggest amount of savings come from cases where the decimal definition is large (like decimal(38,6)) but the values stored are small (like a value of 0.0) or there is a large number of repeated values or data is sparsely populated.

Link

Scalable Shared Databases

• Through Scalable Shared Databases one can mount the same physical drives on commodity machines and allow multiple instances of SQL Server 2005 to work off of the same set of data files. The setup does not require duplicate storage for every instance of SQL Server and allows additional processing power through multiple SQL Server instances that have their own local resources like cpu, memory, tempdb and potentially other local databases.

Link

Get a list of column headers in vertical format:

Copy column names in grid results

Tools - Options - Query Results - SQL Server - Results to Grid tick "Include column headers when copying or saving the results"

you will need to make a new connection at this point, then run your query

Now when you copy the results from the grid, you get the column headers

Also If you then copy the results to excel

Copy col headers only

Paste Special (must not overlap copy area)

tick "Transpose"

OK

[you may wish to add a "," and autofill down at this point]

You have an instant list of columns in vertical format

Execute a stored proc and capture the results in a (temp) table for further processing, e.g.:

INSERT INTO someTable EXEC sp_someproc

Example: Shows sp_help output, but ordered by database size:

CREATE TABLE #dbs
(
	name nvarchar(50),
	db_size nvarchar(50),
	owner nvarchar(50),
	dbid int,
	created datetime,
	status nvarchar(255),
	compatiblity_level int
)
INSERT INTO #dbs EXEC sp_helpdb

SELECT * FROM #dbs 
ORDER BY CONVERT(decimal, LTRIM(LEFT(db_size, LEN(db_size)-3))) DESC

DROP TABLE #dbs

Using the osql utility to run command line queries/scripts/batches

These are some SQL Management Studio hidden features I like.

Something I love is that if you hold down the ALT key while highlighting information you can select columnar information and not just whole rows.

In SQL Management Studio you have predefined keyboard shortcuts:

Ctrl+1 runs sp_who Ctrl+2 runs sp_lock Alt+F1 runs sp_help Ctrl+F1 runs sp_helptext

So if you highlight a table name in the editor and press Alt+F1 it will show you the structure of the table.

did you ever accidentally click on Execute button when u actually wanted to click on :
Debug / Parse / Use Database / Switch between query tabs / etc. ?

Here is a way to move that button someplace safe:

Tools -> Customize . and drag button where you want

You can also :

• add/remove other buttons which are commonly used/unused (applies even to commands within MenuBar like File/Edit)
  
• change icon image of button (see the tiny pig under Change Button Image.. lol)

I would like to recommend a free add-in SSMS Tools Pack which has got bunch of features such as

Code Snippets

You don't need to type SELECT * FROM on your own anymore. Just type SSF and hit enter (which can be customized to any other key. I prefer Tab). Few other useful snippets are

SSC + tab - SELECT COUNT(*) FROM

SST + tab - SELECT TOP 10 * FROM

S + tab - SELECT

I + tab - INSERT

U + tab - UPDATE

W + tab - WHERE

OB + tab - ORDER BY

and the list goes on. You can check and customize the entire list using SSMS Tools Pack Menu

Execution Log History

Have you ever realized that you could have saved an ad hoc query which you wrote few days back so that you don't need to reinvent the wheel again? SSMS Tools pack saves all your execution history and you can search based on date or any text in the query.

Search Database Data

This feature helps you to search for the occurence of the string in the entire database and displays the table name and column name with total number of occurences. You can use this feature by right clicking the database in object explorer and selecting Search Database Data.

Format SQL

Sets all keywords to uppercase or lowercase letters. Right click on query window and select Format Text. You can set the Shortcut key in SSMS Tools Menu. But it lacks alignment feature.

CRUD SP Generation

Right click a table, SSMS Tools - > Create CRUD to generate Insert, Update, Delete and Select SP. The content of the SP can be customized using SSMS Tools menu.

Misc

Few other features are

1. Search results in the Grid mode.
2. Generate Insert script from resultset, tables & database.
3. Execution Plan Analyzer.
4. Run one script in multiple databases.

For more information, you can visit their Features page

For SQL Server 2005:

select * from sys.dm_os_performance_counters

select * from sys.dm_exec_requests

@Gatekiller - An easier way to get just the Date is surely

CAST(CONVERT(varchar,getdate(),103) as datetime)

If you don't use DD/MM/YYYY in your locale, you'd need to use a different value from 103. Lookup CONVERT function in SQL Books Online for the locale codes.

Forced Parameterization

• Parameterization allows SQL Server to take advantage of query plan reuse and avoid compilation and optimization overheads on subsequent executions of similar queries. However there are many applications out there that, for one reason or another, still suffer from ad-hoc query compilation overhead. For those cases with high number of query compilation and where lowering CPU utilization and response time is critical for your workload, force parameterization can help.

Link

A few of my favorite things:

Added in sp2 - Scripting options under tools/options/scripting

New security using schemas - create two schemas: user_access, admin_access. Put your user procs in one and your admin procs in the other like this: user_access.showList , admin_access.deleteUser . Grant EXECUTE on the schema to your app user/role. No more GRANTing EXECUTE all the time.

Encryption using built in encryption functions, views(to decrypt for presentation), and base tables with triggers(to encrypt on insert/update).

OK, here's my 2 cents:

http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/

I am too lazy to re-write the whole thing here, so please check my post. That may be trivial to many, but there will be some who will find it a "hidden gem".

EDIT:

After a while, I decided to add the code here so you don't have to jump to my blog to see the code.

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;

Or, if you want to pull all the User Tables altogether, use CURSOR like this:

DECLARE @tablename VARCHAR(60)

DECLARE cursor_tablenames CURSOR FOR
SELECT name FROM AdventureWorks.sys.tables

OPEN cursor_tablenames
FETCH NEXT FROM cursor_tablenames INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT  t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE],   CAST(p.PRECISION AS VARCHAR) +‘/’+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
FROM AdventureWorks.sys.objects AS t
JOIN AdventureWorks.sys.columns AS c
ON t.OBJECT_ID=c.OBJECT_ID
JOIN AdventureWorks.sys.types AS p
ON c.system_type_id=p.system_type_id
WHERE t.name = @tablename
AND t.type_desc=‘USER_TABLE’
ORDER BY t.name ASC

FETCH NEXT FROM cursor_tablenames INTO @tablename
END

CLOSE cursor_tablenames
DEALLOCATE cursor_tablenames

ADDITIONAL REFERENCE (my blog): http://dbalink.wordpress.com/2009/01/21/how-to-create-cursor-in-tsql/

Not undocumented

RowNumber courtesy of Itzik Ben-Gan http://www.sqlmag.com/article/articleid/97675/sql_server_blog_97675.html

SET XACT_ABORT ON rollback everything on error for transactions

all the sp_'s are helpful just browse books online

keyboard shortcuts I use all the time in management studio F6 - switch between results and query Alt+X or F5- run selected text in query if nothing is selected runs the entire window Alt+T and Alt+D - results in text or grid respectively

I find sp_depends useful. It displays the objects which depend on a given object, e.g.

exec sp_depends 'fn_myFunction' 

returns objects which depend on this function (note, if the objects have not originally been run into the database in the correct order this will give incorrect results.)

In SQL Server 2005 you no longer need to run the sp-blocker-pss80 stored procedure. Instead, you can do:

exec sp_configure 'show advanced options', 1;
reconfigure;
go
exec sp_configure 'blocked process threshold', 30;
reconfigure; 

You can then start a SQL Trace and select the Blocked process report event class in the Errors and Warnings group. Details of that event here.

Some undocumented ones are here: Undocumented but handy SQL server Procs and DBCC commands

use db
go      
DECLARE @procName varchar(100)      
DECLARE @cursorProcNames CURSOR      
SET @cursorProcNames = CURSOR FOR      
select name from sys.procedures where modify_date > '2009-02-05 13:12:15.273' order by modify_date desc     
      
OPEN @cursorProcNames      
FETCH NEXT      
FROM @cursorProcNames INTO @procName      
WHILE @@FETCH_STATUS = 0      
BEGIN      
-- see the text of the last stored procedures modified on 
-- the db , hint Ctrl + T would give you the procedures test 
set nocount off;      
exec sp_HelpText @procName --- or print them      
-- print @procName      
      
FETCH NEXT      
FROM @cursorProcNames INTO @procName      
END      
CLOSE @cursorProcNames      
      
select @@error  

use db
go 

select o.name 
, (SELECT [definition] AS [text()] 
     FROM sys.all_sql_modules 
     WHERE sys.all_sql_modules.object_id=a.object_id 
     FOR XML PATH(''), TYPE
  )  AS Statement_Text
 , a.object_id
 , o.modify_date 

 FROM sys.all_sql_modules a 
 LEFT JOIN  sys.objects o ON a.object_id=o.object_id 
 ORDER BY  4 desc

--select * from sys.objects

Returing results based on a pipe delimited string of IDs in a single statmeent (alternative to passing xml or first turning the delimited string to a table)

Example:

DECLARE @nvcIDs nvarchar(max)
SET @nvcIDs = '|1|2|3|'

SELECT C.*
FROM tblCompany C
WHERE @nvcIDs LIKE '%|' + CAST(C.CompanyID as nvarchar) + '|%' 

I use to add this stored procedure to the master db,

Improvements:

• Trim on Host name, so the copy-paste works on VNC.
• Added a LOCK option, for just watching what are the current locked processes.

Usage:

• EXEC sp_who3 'ACTIVE'
• EXEC sp_who3 'LOCK'
• EXEC sp_who3 spid_No

That's it.

CREATE procedure sp_who3
       @loginame sysname = NULL --or 'active' or 'lock'
as

declare	 @spidlow	int,
		 @spidhigh	int,
		 @spid		int,
		 @sid		varbinary(85)

select	 @spidlow	=     0
		,@spidhigh	= 32767


if @loginame is not NULL begin
	if upper(@loginame) = 'ACTIVE' begin
		select spid, ecid, status
			, loginame=rtrim(loginame)
			, hostname=rtrim(hostname)
			, blk=convert(char(5),blocked)
			, dbname = case
							when dbid = 0 then null
							when dbid <> 0 then db_name(dbid)
						end
			  ,cmd
		from  master.dbo.sysprocesses
		where spid >= @spidlow and spid <= @spidhigh AND
			  upper(cmd) <> 'AWAITING COMMAND'
		return (0)
	end
	if upper(@loginame) = 'LOCK' begin
		select spid , ecid, status
			, loginame=rtrim(loginame)
			, hostname=rtrim(hostname)
			, blk=convert(char(5),blocked)
			, dbname = case
							when dbid = 0 then null
							when dbid <> 0 then db_name(dbid)
						end
			  ,cmd
		from  master.dbo.sysprocesses
		where spid >= 0 and spid <= 32767 AND
			  upper(cmd) <> 'AWAITING COMMAND'
		AND convert(char(5),blocked) > 0
		return (0)
	end
	
end

if (@loginame is not NULL
   AND	upper(@loginame) <> 'ACTIVE'
   )
begin
	if (@loginame like '[0-9]%')	-- is a spid.
	begin
		select @spid = convert(int, @loginame)
		select spid, ecid, status
			, loginame=rtrim(loginame)
			, hostname=rtrim(hostname)
			, blk=convert(char(5),blocked)
			, dbname = case
							when dbid = 0 then null
							when dbid <> 0 then db_name(dbid)
						end
			  ,cmd
		from  master.dbo.sysprocesses
		where spid = @spid
	end
	else
	begin
		select @sid = suser_sid(@loginame)
		if (@sid is null)
		begin
			raiserror(15007,-1,-1,@loginame)
			return (1)
		end
		select spid, ecid, status
			, loginame=rtrim(loginame)
			, hostname=rtrim(hostname)
			, blk=convert(char(5),blocked)
			, dbname = case
							when dbid = 0 then null
							when dbid <> 0 then db_name(dbid)
						end
			   ,cmd
		from  master.dbo.sysprocesses
		where sid = @sid
	end
	return (0)
end


/* loginame arg is null */
select spid,
	   ecid,
	   status
	   , loginame=rtrim(loginame)
	   , hostname=rtrim(hostname)
	   , blk=convert(char(5),blocked)
	   , dbname = case
					when dbid = 0 then null
					when dbid <> 0 then db_name(dbid)
				end
	   ,cmd
from  master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh


return (0) -- sp_who

CTRL-E executes the currently selected text in Query Analyzer.

Use

> select * from information_schema

to list out all the databases,base tables,sps,views etc in sql server.

Alternative to Kolten's sp_change_users_login:

ALTER USER wacom_app WITH LOGIN = wacom_app

BCP_IN and BCP_OUT perfect for BULK data import and export

SQL Server Management Studio keyboard shortcuts... that will enable quicker and faster results in day-to-day works. http://sqlserver-qa.net/blogs/tools/archive/2007/04/25/management-studio-shortcut-keys.aspx

master..spt_values (and specifically type='p') has been really useful for string splitting and doing 'binning' and time interpolation manipulation.

You can create a comma separated list with a subquery and not have the last trailing comma. This has been said to be more efficient than the functions that were used before this became available. I think 2005 and later.

SELECT 
	Project.ProjectName,
	(SELECT
		SUBSTRING(
			(SELECT ', ' + Site.SiteName
			FROM Site
			WHERE Site.ProjectKey = Project.ProjectKey
			ORDER BY Project.ProjectName
	FOR XML PATH('')),2,200000)) AS CSV	
FROM Project

You can also use FOR XML PATH with nested queries to select to XML which I have found useful.

sp_lock: displays all the current locks. The returned data can be further queried as:

spid - use it with sp_who to see who owns the lock.

objid - use it with select object_name(objid) to see which database object is locked.

I use SSMS to find text in files on the OS harddrive. It makes it super easy to write regex and sift through any directory to replace or find text. I always found this easier then using windows.