SELECT a.val, a.code, a.description
FROM 
   (SELECT session_id, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null
	FROM sys.dm_exec_sessions
	WHERE session_id = @@SPID) p
UNPIVOT
   (code FOR setting IN 
      (quoted_identifier, arithabort, ansi_null_dflt_on, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null)
)AS unpvt
CROSS APPLY
(
SELECT 1 as val, 'DISABLE_DEF_CNST_CHK' as code,'Controls interim or deferred constraint checking.' as description
UNION ALL SELECT 2,'IMPLICIT_TRANSACTIONS','For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.'
UNION ALL SELECT 4,'CURSOR_CLOSE_ON_COMMIT','Controls behavior of cursors after a commit operation has been performed.'
UNION ALL SELECT 8,'ANSI_WARNINGS','Controls truncation and NULL in aggregate warnings.'
UNION ALL SELECT 16,'ANSI_PADDING','Controls padding of fixed-length variables.'
UNION ALL SELECT 32,'ANSI_NULLS','Controls NULL handling when using equality operators.'
UNION ALL SELECT 64,'ARITHABORT','Terminates a query when an overflow or divide-by-zero error occurs during query execution.'
UNION ALL SELECT 128,'ARITHIGNORE','Returns NULL when an overflow or divide-by-zero error occurs during a query.'
UNION ALL SELECT 256,'QUOTED_IDENTIFIER','Differentiates between single and double quotation marks when evaluating an expression.'
UNION ALL SELECT 512,'NOCOUNT','Turns off the message returned at the end of each statement that states how many rows were affected.'
UNION ALL SELECT 1024,'ANSI_NULL_DFLT_ON','Alters the session''s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.'
UNION ALL SELECT 2048,'ANSI_NULL_DFLT_OFF','Alters the session''s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.'
UNION ALL SELECT 4096,'CONCAT_NULL_YIELDS_NULL','Returns NULL when concatenating a NULL value with a string.'
UNION ALL SELECT 8192,'NUMERIC_ROUNDABORT','Generates an error when a loss of precision occurs in an expression.'
UNION ALL SELECT 16384,'XACT_ABORT','Rolls back a transaction if a Transact-SQL statement raises a run-time error.'
      ) a
WHERE  unpvt.setting = a.code
AND unpvt.code = 1
ORDER BY a.val;
GO