Hidden Features of SQL Server

Question

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
• sp_tables: return a list of all tables
• 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

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

Answer 1

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

Answer 2

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

Answer 3

Simple encryption with EncryptByKey

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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())

Answer 9

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"

Answer 10

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

Answer 11

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.

Answer 12

Return Date Only

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

or

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

Answer 13

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 )

Answer 14

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

Answer 15

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

Answer 16

For SQL Server 2005:

select * from sys.dm_os_performance_counters

select * from sys.dm_exec_requests

Answer 17

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

Answer 18

I find this small script very hand 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

Answer 19

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 = @<a href="#121613">i </a>
   EXEC ( 'DROP PROCEDURE ' + @name ) 
   SET @i = @i-1 
END

DROP TABLE #tmp

Answer 20

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)

Answer 21

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'

Answer 22

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"

Answer 23

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

Answer 24

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

Answer 25

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.

Answer 26

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

sp_help 'TableName'

Answer 27

@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.

Answer 28

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.

Answer 29

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

Answer 30

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

Answer 31

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

Answer 32

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

Answer 33

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

Answer 34

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 with the commnad

select * from sys.dm_exec_query_stats 
order by execution_count desc

Answer 35

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

Answer 36

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

Answer 37

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!

Answer 38

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

Answer 39

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).

Answer 40

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/

Answer 41

sp_executesql

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

Answer 42

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.

Answer 43

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

Answer 44

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.)

Answer 45

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.

Answer 46

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.

Answer 47

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

UPDATE

Looks like the post lost my example somehow. Here it is again..

declare @orderby varchar(10)

set @orderby = 'NAME'

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

Answer 48

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

Answer 49

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

Answer 50

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.

Answer 51

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

Answer 52

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

Answer 53

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

Answer 54

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,"

Answer 55

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) + '|%'

Answer 56

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

Answer 57

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.

Answer 58

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

Answer 59

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
• Ctrl-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

Answer 60

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

Answer 61

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

Answer 62

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

Answer 63

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.

Answer 64

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

Answer 65

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.

Answer 66

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')

Answer 67

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.

Answer 68

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.

Answer 69

Use

select * from information_schema

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

Answer 70

Alternative to Kolten's sp_change_users_login:

ALTER USER wacom_app WITH LOGIN = wacom_app

Answer 71

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.

Answer 72

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.

Answer 73

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: http://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

Answer 74

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

Answer 75

BCP_IN and BCP_OUT perfect for BULK data import and export