Have you ever scripted a stored procedure? Or dealt with indexed views?  Or wondered why Management Studio says you can't index or alter a table column?  Then you've been bitten by one or more of the the ANSI_NULLS, ANSI_PADDING, or QUOTED_IDENTIFIER settings!  These settings determine how (and sometimes if) SQL Server can process a query, and the wrong settings can cause a terminal error.  Wouldn't it be nice to know what your current settings are?

With SQL Server 2005, the sys.dm_exec_sessions DMV can provide nearly all of them.  Since it's documented in Books Online I'll let the reader practice their reference skills on it. :)  But for those who are still using SQL Server 2000, and don't have this DMV available, what can be done?

Two options, actually.  One is the SESSIONPROPERTY() function, and the other is the DBCC USEROPTIONS command.  (See BOL for details)  While both are useful, they each have some limitations.  DBCC USEROPTIONS outputs results in tabular format, but they can't be queried unless you use an INSERT…EXEC statement with dynamic SQL.  It also only shows settings that are SET to ON.  It's not a big deal to work out those settings that are off, but it's a pain. SESSIONPROPERTY() will return whether a setting is either ON or OFF (actually 1 or 0, respectively) but it does not include settings like NOCOUNT, XACT_ABORT, or IMPLICIT_TRANSACTIONS.

Fortunately there is another function, @@OPTIONS, that can be used to return all those settings, and it's available in SQL Server 2000!  @@OPTIONS returns a bitmasked integer value, so it's not intuitive, but with a little bitwise magic (and BOL details) you can make it very useful indeed:

CREATE VIEW Settings(Setting,Status) AS
SELECT name, CASE WHEN @@OPTIONS & number=0 THEN 'OFF' ELSE 'ON' END Status 
FROM master.dbo.spt_values 
WHERE type='SOP'
AND number>0
GO

You can then query it like any other view, and play with settings and watch the resulting changes:

SET NOCOUNT ON
SELECT * FROM Settings WHERE Setting='NOCOUNT'
SET NOCOUNT OFF
SELECT * FROM Settings WHERE Setting='NOCOUNT'

You should also check out Martin Bell's blog post on user options to see a similar technique using sys.dm_exec_sessions, and Greg Robidoux's article on MSSQLTips for another way to display these settings using @@OPTIONS.

Now you may want the data returned as a single row with each setting in a separate column, to more closely mimic the sys.dm_exec_sessions DMV.  I'll leave that as an exercise for the reader, but I may attempt such a version in a later blog post.  You may prefer this as a user-defined function, or even a stored procedure, in any case it's easy to modify.  Since this would qualify as a "system" view, you may want to place it in master and mark it as a system object, but I've not had any luck getting that to work properly.  The easiest option is to create a stored procedure in master with an "sp_" prefix so that it can run from any database.  Otherwise you'll need to reference the view with a 3-part name. (or, ugh, create it in every database)

Hopefully this will inspire you to poke around Books Online for other "unknown" functions (the @@ ones in particular) that could make life a little easier.  I should find a few more that I can blog about in the future.

UPDATE: As Peso suggested in his comment, I updated the view definition to use spt_values.  Thanks Peter!