This is my top 10 Obscure T-SQL commands list. This list represents commands that I don’t use very often, but when I need them I need them.   I find it to be a little blessing that they exist.  Strangely a good number of these commands turned out to be “undocumented”, this is not a top 10 undocumented commands list, it just that a few of them happen to be undocumented.

10.) DBCC OutputBuffer – Some times and I can’t give a great example, I need to be able to look at the output buffer of a process.  This normally occurs when I have a very long running stored procedure or process that doesn’t have any kind of logging built into where I can check to see where it’s at from time to time.  Basically I look at the code that is running if possible, compare it to what is in the output buffer and then take an educated guess on where I think it is in the procedure.  I’m sure there are a ton of other good uses for this command but that’s where it gets most of its use in my day to day. (http://msdn.microsoft.com/en-us/library/ms189821.aspx )

Example:
DBCC outputbuffer(53)

9.) xp_fixedDrives – this useful little undocumented extended procedure tells you one thing and one thing only, the free space  and the letters of the drives mounted on the server that is hosting the instance of SQL Server you run this command on.  I don’t have a whole lot of use for this procedure normally, but if I am in an environment where I do not have access to xp_cmdshell then this can be a huge boon.  It can be especially if you need to quickly check the drive space of all the drives to make a sure some processes isn’t eating up all of the disk space, like a trace.

Example:
EXEC Xp_fixeddrives

8.) xp_dirTree – when I find that I am in a situation that I need to use xp_fixedDrives I also find that I need to use xp_dirTree.  This procedure gives you a hierarchical listing of a drive letter with or without a subdirectory.  You can also specify the depth of the subdirectories that you would like it to traverse. (http://www.sql-server-performance.com/articles/dev/extended_stored_procedures_p1.aspx)

Example:
EXEC Xp_dirtree ’c:’, 1, 1

7.) fn_trace_getInfo – nothing undocumented here, just a very useful procedure.  Want to know what traces are running on your system or at least have there definitions stored on your system, fn_trace_getInfo will answer your question.  I used to be profiler only kind of man when it came to sql traces but over the past few months I have really come to appreciate the T-SQL based trace functionality.  Its simple elegant and easy to use, fn_trace_getInfo helps those of us without the best memory remember what we did yesterday or possible a few days ago as long as you didn’t delete the trace definition. (http://msdn.microsoft.com/en-us/library/ms173875.aspx)

Example:
SELECT *
FROM   ::fn_trace_getinfo(0)

6.)fn_trace_getTable – keeping with the T-SQL based trace commands, fn_trace_getTable is great, it allows you to read all of your *.trc files in a tabular form.  This result set can be pushed into a table very easily. It allows me to run all different kinds of quires over my trace files to see what is going on.  Once again I don’t do this very often and there are a number of third party tools to help with managing traces for gathering performance metrics on queries. When I don’t have those as an option and Profiler for one reason or another doesn’t fit the bill, fn_trace_getTable makes life a little easier. (http://msdn.microsoft.com/en-us/library/ms188425.aspx )

Example:
SELECT *
INTO   trace.mytracetable
FROM   ::fn_trace_gettable(‘c:\myTraceFile’, DEFAULT) b

5.) DBCC InputBuffer – before there was DMVs for SQL Text we had DBCC InputBuffer and we all loved it.  Seriously this was the way to see what a particular SPID was executing that was it, that or you had to have a trace running before hand.   (http://msdn.microsoft.com/en-us/library/ms187730.aspx )

Example:
SELECT *
INTO   trace.mytracetable
FROM   ::fn_trace_gettable(‘c:\myTraceFile’, DEFAULT) b

4.) Raiserror – Raiserror by itself its not really an obscure piece of T-SQL, but using it with a severity of -1 so it uses the default severity in the sys.messages table is not run of the mill. I just came across this recently and it is great.  Long story short if you create a message in the sys.messages. You have to ask yourself why would sp_addMessages require a severity when the raiserror command requires you to input a severity level.  How can I ever call that message without passing in the severity level to raiserror that trumps the default?  Well if you pass a -1 as the severity it uses the default value. The documentation states that severity levels less than 0 are treated as 0 I don’t really see how that’s true. (http://msdn.microsoft.com/en-us/library/ms178592.aspx )

Example:
RAISERROR (38001,-1, 1)

3.) sp_who2 – wow I’ve been relying on this command for a long long time.  I think this one is a go-to for everyone.  It also is pushing the boundaries on “Obscure” but I figure it’s been a favorite for a lone time.  It is being replaced by sys.dm_exec_*  and by sp_whoIsActive in my repertoire but it hasn’t faded completely so sp_who2 gets an honorable mention.  (http://sqlserverplanet.com/dba/using-sp_who2/ )

Example:
EXEC Sp_who2

2.) …For XML  – I haven’t had a lot of reason to use this command in my career.  If I had to guess I’ve used it on about ½ dozen separate projects/tasks for very specific requirements. When I have needed it, the ability to create XML documents on the fly from SQL Server queries is a huge time saver.  (http://msdn.microsoft.com/en-us/library/ms190922.aspx )

Example:
SELECT name,
object_id
FROM   sys.tables
FOR XML RAW(‘sys.tables’), ELEMENTS, ROOT(‘Root’)

1.) This was a hard one to pick, but since I’ve recently done a lot of CDC work, and it’s come in handy a life time ago with replication, it came down to DBCC OPENTRAN.  This command simply shows you the oldest active transaction and the oldest non distributed transaction.  It’s that second part that comes in very handy if you are working in an environment that has SQL Replication or has CDC turned on.  If there is a transaction that can not be distributed for either replication or CDC it can may your transaction log fill up.  This can give you quick view into what is going on. (http://msdn.microsoft.com/en-us/library/ms182792.aspx )

Example:
DBCC opentran(MASTER)

If you see something you don’t like in the list or the order of the list or if you think some command is missing, let me know and drop a comment below.