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

Absher

If known, it would be nice to include the applicable versions with each answer. (2000 and up, 2005, 2000 only, etc.) – Tubule

There is a lot of goodness in this question. Please do not delete it! :-) – Absher

Answer 1

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.

Excide

Answer 2

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

Joijoice

Answer 3

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

Deaton

You can get the name of the table in the query by using single quotes around the question mark. sp_msforeachtable "select count(*), '?' as tabenm from ?" – Brierwood

Answer 4

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"

Disheveled

I made Application Name a requirement at my company. Every new app must have a unique name. Makes tracking down which app locked/broke something a lot easier. – Leone

Application Name is also available as a filter in profiler. It helps a lot if you want to only see your queries and not the queries of your coworkers. – Synectics

Answer 5

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

Absher

Answer 6

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

Deaton

Great for small result sets. I wouldn't use it on a table with more than 10000 rows unless you've got time to spare – Tessy

I've used it on tables much larger than that, and it wasn't too slow. – Deaton

What's the purpose of the CHECKSUM()? You can order by just NEWID(). – Phosphoroscope

I've even seen decent results on 100,000,000 (100 mil) rows, w/o CHECKSUM(). Also, I have to ask as well, why not just ORDER BY NEWID? – Afb

@Prashant : this question is about SQL server ..! – Deaton

@GateKiller: I've rolled back your edit, because the Checksum() is not a mistake; it reduces the size of the sort column. – Deaton

I'd used rand() for that random order. This way will always be random in every run. select * from table order by cast(RAND(DATEPART(ms, GETDATE()) * id) * 1000000 as int) , id asc – Openair

Answer 7

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.

Thoughtful

Answer 8

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

Bascio

I voted this up, but then tried it in MSSQL 2005, and it doesn't work. 2008 only? – Banting

Yes, it's a new feature of 2008. – Bascio

This was a feature that I missed when I came from DB2 to SQL Server. In DB2, there was a significant speed improvement when using this instead of individual insert statements – Hazan

Answer 9

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

sp_help 'TableName'

Novgorod

Combine this tip with its shortcut key! First highlight a tablename and then hit ALT+F1 – Stria

Answer 10

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

Reciprocation

Nice one! The correct link is msdn.microsoft.com/en-us/library/ms174415(SQL.90).aspx (2005 version) – Absher

You're right, that was the 2008 version of the docs, even though the pages are pretty much identical. Fixed now. – Reciprocation

Answer 11

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

Absher

Answer 12

The spatial results tab can be used to create art.

enter link description here http://michaeljswart.com/wp-content/uploads/2010/02/venus.png

Infracostal

I saw Jesus in my query results! – Felker

Pfff... What chump wastes his time messing with the spatial results tab. Oh wait... You know, I thought that post looked familiar, now I remember why. – Stria

Answer 13

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.

Jaques

Answer 14

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!

Calcific

Answer 15

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'

Evanevander

I have had this proc not work before, and I had to change the objects ownership to a temp user, drop the original user, re -add the original and assign the ownership back. Ugh... – Edulcorate

Answer 16

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,

Rescissory

you can also use COALESCE() to do the same thing without the need to initialize the variable. SELECT @nvcConcatonated = COALESCE(@nvcConcatonated+',','')+CAST(C.CompanyName as VARCHAR(255)) FROM... – Irish

This also works in an update statement. Sometimes useful for doing things like concatenating a list of ID's that were updated. – Badge

Answer 17

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

Mcquade

These allow you to produce a checksum for all the data in the table. It is a simple and quick way to check if two rows or two tables are the same. – Mcquade

Answer 18

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

Mcquade

Is there a one-liner, or a drop database parameter that does this for me? I notice that if you attempt to 'delete database' through the ui, there's a checkbox for 'close existing connections' which implies that it's a boolean parameter. – Cainozoic

Actually, I just found a two line solution. ALTER DATABASE [@DATABASE_NAME@] SET READ_ONLY WITH ROLLBACK IMMEDIATE --this disconnects all users ALTER DATABASE [@DATABASE_NAME@] SET READ_WRITE WITH ROLLBACK IMMEDIATE DROP DATABASE [@DATABASE_NAME@] – Cainozoic

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE will prevent any new connections from occurring, too. – Paschal

Answer 19

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)

Novgorod

Don't know why, but sp_helptext output is a bit goofy on any overly long lines in the original. When scripting Sprocs this doens't happen, so maybe there is another, more robust, export mechanism? sp_helptext 'MyView' also useful. – Thrower

I'm not sure what you mean. For me, the SPs code are outputed with the same format I have scripted them in the original file (with all the CRs, etc) – Novgorod

I don't recall the exact details, but it has to do with the way the text is stored--something about page size, I believe. The output is mostly correct, but every now & then you get an extra line break. – Untruthful

Answer 20

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

Scutage

Sadly can't be used with @TableVariable – Thrower

The pain with this very useful technique is that unlike most #tables, you have to fully define all the columns. The lazy way of doing this is to create the #table inside the proc you are calling right at the end, then sp_help in tempdb, copy and paste, remove code from proc. Done – Remus

Answer 21

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.

Kasher

wouldn't be simpler if they used syntactic suger in sql engine to parse it syntax word as "RowNumberInTable" – Riparian

+1 for window functions. You can do things OVER a subset of records by using OVER (PARTITION BY ...) msdn.microsoft.com/en-us/library/ms189461%28v=SQL.100%29.aspx – Tamarah

Answer 22

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

Absher

I must be having a dumb moment (no, really). Can you tell me where we can use this? – Dedradedric

Answer 23

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: SQL Server - What is the date/time of the last inserted row of a table?

Available in SQL 2005 and later

Hazan

Answer 24

Return Date Only

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

or

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

Mcquade

Short version - SELECT CAST(FLOOR(CAST(@DateTime AS FLOAT))AS DATETIME) – Wariness

Hell yes. CASTFLOORCAST rules. – Edulcorate

Can't find a reference to it, but I seem to remember tests that suggested SELECT DateAdd(Day, 0, DateDiff(Day, 0, @DateTime)) was faster. Happy to be enlightened, either way! – Thrower

Found this sqlteam.com/forums/topic.asp?TOPIC_ID=35296#107617 but it didn't include the CAST/FLOOR method. An informal test on a medium sized recordset suggests DATEADD may be about 7% faster than CAST/FLOOR - not enough to worry about for most situations – Thrower

I've added the other method, however; my quick testing shows that the cast floor method is 800 Nanoseconds quicker. So nothing in it really. – Mcquade

Some other "Date flooring" here: https://mcmap.net/q/128436/-floor-a-date-in-sql-server – Thrower

I've had problems with casting dates to float and back not being accurate. – Paschal

Answer 25

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

Nonalignment

Answer 26

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

Absher

Answer 27

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"

Tauromachy

I use to configure the USE command also, for moving along the db's – Ventricle

Answer 28

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 )

Mcquade

Fine but index on TransDate would not be used. I would rather write – Sausauce

where TransDate >= convert(datetime, floor(convert(float, dateadd(day, -datepart(weekday, @date)+1, @date)))) and TransDate >= convert(datetime, floor(convert(float, dateadd(day, 7-datepart(weekday, @date)+1, @date)))) – Sausauce

correction: where TransDate >= convert(datetime, floor(convert(float, dateadd(day, -datepart(weekday, @date)+1, @date)))) and TransDate < convert(datetime, floor(convert(float, dateadd(day, 7-datepart(weekday, @date)+1, @date)))) – Sausauce

Answer 29

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

Absher

Answer 30

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.

Loanloanda

You can also just type NULL in the cell. – Coonhound

Answer 31

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.

Calamint

Answer 32

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

Baroja

This technique is very useful custom paging is been implemented with sorting in GridView etc. – Proa

You can also use a case statement in a group by clause – Schismatic

Answer 33

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

Middy

Answer 34

Simple encryption with EncryptByKey

Tessy

Answer 35

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

Coonhound

Answer 36

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)

Ectomorph

Answer 37

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.

Bascio

Answer 38

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

Sillimanite

Answer 39

sp_executesql 

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

Novgorod

Answer 40

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

Wariness

Answer 41

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

Pentstemon

Answer 42

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

Defalcate

sp_helptext 'myproc_Proc1' is shorter – Novgorod

Answer 43

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

Anchovy

I love sp_who2, but sp_WhoIsActive by Adam Machanic is even better! Check out v10.x from the following link: tinyurl.com/WhoIsActive – Glovsky

Answer 44

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.

Bascio

Answer 45

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.

Encephalic

Answer 46

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

Sheya

Answer 47

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

Epigenous

CLR Integration with SQL Server is the one of those "best things Microsoft ever did", ranking up there with creating the CLR itself and the C# language. – Rhodic

Answer 48

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.

Loanloanda

Answer 49

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

Stereotypy

Answer 50

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

Absher

Answer 51

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.

Uzia

Answer 52

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

Monteverdi

Answer 53

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)

Jaine

Answer 54

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.

Dendrite

Didn't get your first feature of holding down ALT key. Can you please elaborate. – Proa

Answer 55

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

Azygous

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

Polyphony

Answer 57

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

Remus

Answer 58

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

Absher

Answer 59

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

Absher

Answer 60

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

Absher

Answer 61

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

Defalcate

Answer 62

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.

Coonhound

Answer 63

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.

Together

Answer 64

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.

Scaphoid

Answer 65

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.

Dykstra

Answer 66

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

Wester

Answer 67

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

Cacoepy

Answer 68

BCP_IN and BCP_OUT perfect for BULK data import and export

Garnett

Answer 69

Alternative to Kolten's sp_change_users_login:

ALTER USER wacom_app WITH LOGIN = wacom_app

Faceless

Answer 70

Use

select * from information_schema

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

Ancheta

How is this hidden? The information_schema view is part of the SQL 92 standard. – Insurgence

Answer 71

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

Salpingectomy

It didn't in Sql Server 2008. Is it specific to some version? I think CRTL+SHIFT+Q does it. – Proa

Answer 72

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

Ventricle

Answer 73

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

Rescissory

SELECT C.* FROM tblCompany C WHERE @nvcIDs IN(1,2,3) also works, without all the casting and string-searching. – Banting

Don't ever do this on tables which are of any considerable size. – Waylonwayman

@richardtallent: This technique is useful when you get the ID's dynamically from code. The even better way would be to convert the pipe separated IDs to a temp table and use IN operator in where clause than. – Proa

Answer 74

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

Cromorne

Answer 75

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  

Grizel

Answer 76

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

Dawn

Answer 77

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.

Torpedoman

Answer 78

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

Marston

I've found sp_depends a bit unreliable in complex enterprise scenarios. – Sillimanite

@ip-- so have I. For example, if you have a view (A) that depends on another view (B) & run sp_depends on B, A won't show up in the results. – Untruthful

Answer 79

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

Disastrous

Answer 80

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/

Laylalayman

Answer 81

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

Inelastic

Answer 82

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

Absher

Answer 83

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

Bionics

The conversion via VARCHAR is much slower than "CAST(FLOOR(CAST(@DateTime AS FLOAT))AS DATETIME)" or "DateAdd(Day, 0, DateDiff(Day, 0, @DateTime))" (between 5 & 6 times as slow - c.f. sqlteam.com/forums/topic.asp?TOPIC_ID=35296#107617) and config dependant – Thrower

I always use 106 to convert dates. That uses the month name instead of a number and is totally unambiguous. – Prue

Answer 84

For SQL Server 2005:

select * from sys.dm_os_performance_counters

select * from sys.dm_exec_requests

Irish